How to check if month/week/year has been selected in relative date slicer

[Anonymous]

Hi All, 

We are trying to calculate change% from the average over the time period selected.

Data set - 

Dec- 5

Jan - 4

Feb - 6

if user selects "last 3 months" 

sum - 15

avg - 5

change% - %change of the last value(6) from the average (5)

=20%

How to calcuate last value based on the user selection? So if user selects "last 3 year", need to show sum in that period & for delta calculation need "SUM IN LAST 1 YEAR" also.

Please suggest if any other slicer/workaround can help here.

[Anonymous]

try this:

WasWeek =

IF(     

   YEAR ( [Fecha] ) <= YEAR ( TODAY () )

;IF(

    MONTH ( [Fecha] ) <= MONTH ( TODAY () );

        IF(

            WEEKDAY( ( [Fecha] ) < WEEKDAY( ( TODAY () )));"Yes";"No"

        )

    ;"No")

        ;"No")

[amitchandak]

You can take advantage of rolling formula, Change no and period as per need. use with the Calendar table. get you day, moth year slicer from date tbale

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-30,Day))  

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,Day))  

Rolling 4 week same week day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-28,Day),filter(Date, WeekDay('Date'[Date])=max( WeekDay('Date'[Date]))))  

3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

Thanks Amit for the help. I do have a calendar table in my model. 

To determine which formula to use, how do we determine if week or year or month has been selected ?

[Icey · Accepted solution]

Hi @Anonymous ,

I'm afraid there is no DAX function meeting your requirements.

But you can get the Range of selected date with MAX() and MIN() function.

Or, you can try to create slicers which work like relative date slicer by yourself. For specific operations, you can refer to the attached PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

How do you create the table calendar and week/month/calendar ? 

[Icey]

Hi @Anonymous ,

You can find it in the attached PBIX file above.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2020, 2, 29 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "WeekNum", WEEKNUM([Date],1)
)

Week/Month/Year = 
UNION (
    SUMMARIZE ( 'Calendar', 'Calendar'[YearNum of All Years], "Years", "Years" ),
    SUMMARIZE ( 'Calendar', 'Calendar'[MonthNum of All Years], "Months", "Months" ),
    SUMMARIZE ( 'Calendar', 'Calendar'[WeekNum of All Years], "Weeks", "Weeks" )
)

Best Regards,

Icey

[Anonymous]

Hi @Icey 

Relative date slicer is a smart solution, I was looking for something similar. Thanks, that does help.

[Anonymous]

Thanks @Icey, I am working on that, I will get back to you.