Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
@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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |