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.
My current data time frame runs upto August 8th, but I'm running my report August 17th (today's date), and my Calendar table contains dates up to the end of the year.
My current MTD should sum from August 1 to August 8th. But I would like a measure to show me the same 8 day range of each prior month. July 1 to July 8th, June 1 to June 8th....
My current QTD should sum from July 1 to August 8th. But I would like a measure to show me the same 39 day range of each prior quarter. January 1 to February 8th, April 1 to May 8th...
My current YTD should sum from January 1 to August 8th. But I would like a measure to show me the same day range in each prior year...
TOTALMTD, DATESMTD... functions just provide the full month of prior months. How do I specify the X day range?
Hey,
in addition to what @fhill already suggested,
I would recommend to also have look at this site
http://www.daxpatterns.com/time-patterns/
here a lot of concepts are explained how to use DAX to tackle questions about time intelligence
From my point of view here everything (almost everything) is pictured that has to be solved considering the importanct of time.
Also problems with a leapyear are covered.
Regards
Yes I have put to practice the majority of this site. It was extremely helpful, for all except this partial date frame.
I don't have all your solutions, but hopefull you can use the same methodology to get your other resutls as well... I think Year might be easy based on the value pattern, Quarter might be harder, but see if this helps leads you to where you need to go...
I have a table of Days and Values, i'm sure your data is more complex but it demostrates the concept. ** P.S. If you don't have a value for EVERY day, you may want to RELATED Link the data to a Calendar Table to ensure the DATEDIFF / DATEADD calculations all work. **
** Calculate the number of days since the start of the cureent month vs. TODAY()
DaysIn = DATEDIFF(DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY(),DAY)
** SUM Values for DatesBetween Start of Month (Quarter/Year) & Start of Month DATEADD 'DaysIn'.
SumDaysIn = CALCULATE(SUM(Table1[Value]), DATESBETWEEN(Table1[Date],STARTOFMONTH(Table1[Date]), DATEADD(STARTOFMONTH(Table1[Date]),[DaysIn],DAY)))
Build a Table with Month values, and the SumDaysIn measure will SUM based on the number of days since the start of each month.
Proud to give back to the community!
Thank You!
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 |
---|---|
108 | |
106 | |
87 | |
77 | |
69 |
User | Count |
---|---|
124 | |
112 | |
94 | |
84 | |
75 |