Need to display Last Month MTD on date based selection in the slicer power bi

1
votes

I have a requirement in Power bi that i need to display the last month mtd on basis of selection of date slicer.

here is the details

Ex: jan 1 2020 : 10 cases, jan 2, 2020 : 30 cases.. when i select feb 2 2020 in slicer it should display the jan 2 2020 value not mtd of that month(january)

Thanks, Imran

1
powerbidaxpowerbi-desktop
What if 30-March is selected? – mkRabbani
blank, as there is no data for feb 28 – immu
Create a measure that will return your expected date from previous month based on selected date. Then create another measure for your value and filter data using the date measure. – mkRabbani
Create a measure that will return your expected date from previous month based on selected date.:::::Previous = CALCULATE([Sum_Covid_Cases],PREVIOUSMONTH('Calendar Demo'[Date])) Still getting total value, instead of the previous month , instead of selected date – immu
PREVIOUSMONTH is for whole previous month. – mkRabbani

1 Answers

1
votes

Without using time intelligence functions, you can probably write something like this:

LastMonthMTD =
VAR DateSelected = SELECTEDVALUE ( 'Calendar'[Date] )
VAR PrevMonth = EOMONTH ( DateSelected, -1 )
VAR PrevDate = DATE ( YEAR ( PrevMonth ), MONTH ( PrevMonth ), DAY ( DateSelected ) )
VAR StartDate = EOMONTH ( DateSelected, -2 )
RETURN
    CALCULATE (
        [Sum_Covid_Cases],
        'Calendar'[Date] > StartDate,
        'Calendar'[Date] <= PrevDate
    )

Using time intelligence, you could try something like this:

LastMonthMTD =
CALCULATE (
    [Sum_Covid_Cases],
    DATEADD ( DATESMTD ( 'Calendar'[Date] ), -1, MONTH )
)

I haven't tested these, so let me know if they work or not.