Error in calculating for Previous Period when using custom date period selection

[dyabes]

Hope you are all having a great day!

I am trying to implement this great idea from theh BI Elite team which is using a custom date period selection (MTD, YTD, QTD...), instead of the standard slicer for calendar picking the start date and end date. All is well and good. I wanted then to implement a calculation of Previous Period (adjacent prior period) and Previos Year (same dates last year) based on the date period selection.

I would then plot these measures to compare trends. 

I am having trouble bringing back the row context for dates. Can someone help me?

Here is a link to the PBIX file 

Thank you in advance!

Cheers,

David

[Greg_Deckler · Accepted solution]

@dyabes Here are some typical date intelligence measures written in the form of Calculation groups (below) These will work with calendar dates or fiscal dates. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

YTD

CALCULATE(SELECTEDMEASURE(),

    FILTER(ALL('Dates'),

         'Dates'[CalendarYear] = MAX('Dates'[CalendarYear]) &&

         'Dates'[Date] <= MAX('Dates'[Date])))

MTD

CALCULATE(SELECTEDMEASURE(),

    FILTER(ALL('Dates'),

        'Dates'[CalendarYear] = MAX('Dates'[CalendarYear]) &&

        'Dates'[MonthNumberOfYear] = MAX('Dates'[MonthNumberOfYear])

 && 'Dates'[Date] <= MAX('Dates'[Date])))

QTD

CALCULATE(SELECTEDMEASURE(),

    FILTER(ALL('Dates'),

        'Dates'[CalendarYear] = MAX('Dates'[CalendarYear]) &&

        'Dates'[CalendarQuarter] = MAX('Dates'[CalendarQuarter]) &&

        'Dates'[Date] <= MAX('Dates'[Date])))

PY

VAR __MaxDate = MAX('Dates'[Date])

VAR __MinDate = MIN('Dates'[Date])

RETURN

    CALCULATE(SELECTEDMEASURE(),

        FILTER(ALL('Dates'),

            'Dates'[Date] >=

                DATE(YEAR(__MinDate)-1,MONTH(__MinDate),DAY(__MinDate)) &&

            'Dates'[Date] <=

                DATE(YEAR(__MaxDate)-1,MONTH(__MaxDate),DAY(__MaxDate))))

PY YTD

VAR __MaxDate = MAX('Dates'[Date])

RETURN

    CALCULATE(SELECTEDMEASURE(),

        FILTER(ALL('Dates'),

            'Dates'[CalendarYear] =

                MAX('Order Dates'[CalendarYear])-1 &&

            'Dates'[Date] <=

                DATE(

                    YEAR(__MaxDate)-1, 

                    MONTH(__MaxDate),

                    DAY(__MaxDate))))

YOY

SELECTEDMEASURE()

    - CALCULATE(SELECTEDMEASURE(),'Date Intelligence Group'[Name] = "PY")

YOY %

DIVIDE(

    CALCULATE(SELECTEDMEASURE(),'Date Intelligence Group'[Name] ="YOY"),

    CALCULATE(SELECTEDMEASURE(),'Date Intelligence Group'[Name] ="PY"))

Equivalent measures using DAX Time Intelligence functions are the following:

• Current: SELECTEDMEASURE()
• YTD: CALCULATE(SELECTEDMEASURE(), DATESYTD('Order Dates'[Date]))
• MTD: CALCULATE(SELECTEDMEASURE(), DATESMTD('Order Dates'[Date]))
• QTD: CALCULATE(SELECTEDMEASURE(), DATESQTD('Order Dates'[Date]))
• PY: CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Order Dates'[Date]))
• PY YTD: CALCULATE(SELECTEDMEASURE(),SAMEPERIODLASTYEAR('Order Dates'[Date]),'Time Intelligence Group'[Name]= "YTD")
• YOY: SELECTEDMEASURE()-CALCULATE(SELECTEDMEASURE(),'Time Intelligence Group'[Name] = "PY")
• YOY %: DIVIDE(CALCULATE(SELECTEDMEASURE(),'Time Intelligence Group'[Name] ="YOY"),CALCULATE(SELECTEDMEASURE(),'Time Intelligence Group'[Name] ="PY"))

[dyabes]

Hi @Greg_Deckler -- I went through most of the materials that you provided and they helped me figure out what I need but have expanded my understanding of Time Intelligence and DAX in general. 

Thank you again!

-David