Forum Discussion
Error in calculating for Previous Period when using custom date period selection
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
Anonymous 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-TITHW/m-p/434008YTD
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"))
2 Replies
- Greg_Deckler
Community Champion
Anonymous 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-TITHW/m-p/434008YTD
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"))
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