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.
Hello all,
I have a question that is probably simple, but for two days I have tried so many different things and can't find a solution. I understand the concept of MTD/YTD/QTD totals. However, at my job they like to see each of those values based on the last month completed, or last quarter completed for the QTD total. It's December so it would look like this.
MTD - Month Total for November
YTD - Jan1 - November 30
QTD - Total of last completed quarter.
Below are the measures I have based on true MTD, YTD, QTD and they work perfectly:
MTD Units Actual = CALCULATE([Total Units Actual],DATESMTD(CalendarTable[Date])) YTD Units Actual = CALCULATE([Total Units Actual],DATESYTD(CalendarTable[Date])) QTD Units Actual = CALCULATE([Total Units Actual],DATESQTD(CalendarTable[Date]))
I do have a CalendarTable. So, my question is how can I use these or some other DAX expression to accomplish what I described above. Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous,
Sorry for my misunderstanding. Could you try the formulas below to see if it works?
Previous Month Units = CALCULATE ( [Total Units Actual], FILTER ( ALL(CalendarTable), YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () ) && MONTH ( CalendarTable[Date] ) = MONTH ( TODAY () ) -1 ) )
YTD Units = CALCULATE ( [Total Units Actual], FILTER ( ALL(CalendarTable), YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () ) && MONTH ( CalendarTable[Date] ) <= MONTH ( TODAY () ) -1 ) )
QTD Units = CALCULATE ( [Total Units Actual], FILTER ( ALL ( CalendarTable ), ENDOFQUARTER ( CalendarTable[Date] ) = STARTOFQUARTER ( TODAY () ) - 1 ) )
Regards
Hi @Anonymous,
Sorry for my misunderstanding. Could you try the formulas below to see if it works?
Previous Month Units = CALCULATE ( [Total Units Actual], FILTER ( ALL(CalendarTable), YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () ) && MONTH ( CalendarTable[Date] ) = MONTH ( TODAY () ) -1 ) )
YTD Units = CALCULATE ( [Total Units Actual], FILTER ( ALL(CalendarTable), YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () ) && MONTH ( CalendarTable[Date] ) <= MONTH ( TODAY () ) -1 ) )
QTD Units = CALCULATE ( [Total Units Actual], FILTER ( ALL ( CalendarTable ), ENDOFQUARTER ( CalendarTable[Date] ) = STARTOFQUARTER ( TODAY () ) - 1 ) )
Regards
Thanks again for you replies. It worked perfectly. Any idea how I might refine this just a bit, so that if it's January, then show the last month as December of prior year?
MTD Units - Actual Prior Month = IF ( MONTH ( TODAY () ) = 1, CALCULATE ( [Total Units - Actual], FILTER ( DateTable, YEAR ( DateTable[Date] ) = YEAR ( TODAY () ) - 1 && MONTH ( DateTable[Date] ) = MONTH ( TODAY () ) -1 ) ), CALCULATE ( [Total Units - Actual], FILTER ( DateTable, YEAR ( DateTable[Date] ) = YEAR ( TODAY () ) && MONTH ( DateTable[Date] ) = MONTH ( TODAY () ) - 1 ) ) )
Nevermind, I figured it out! I just used the IF funtion inside the measure!
Hi @Anonymous,
Try the formulas below, which should give your expected result.
MTD Units Actual = CALCULATE ( [Total Units Actual], FILTER ( CalendarTable, YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () ) && MONTH ( CalendarTable[Date] ) = MONTH ( TODAY () ) && CalendarTable[Date] <= TODAY () ) )
YTD Units Actual = CALCULATE ( [Total Units Actual], FILTER ( CalendarTable, YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () ) && CalendarTable[Date] <= TODAY () ) )
QTD Units Actual = CALCULATE ( [Total Units Actual], FILTER ( CalendarTable, YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () ) && CalendarTable[Date] >= STARTOFQUARTER ( TODAY () ) && CalendarTable[Date] <= TODAY () ) )
Regards
These measures work great, except they are still showing the true MTD, YTD, and QTD. Any thoughts on what I need to do so the prior month totals? I tried the below and still didn't work
Thanks in advance!
MTD Units Actual = CALCULATE ( [Total Units Actual], FILTER ( CalendarTable, YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () ) && MONTH ( CalendarTable[Date] ) = MONTH ( TODAY ()-1) && CalendarTable[Date] <= TODAY () ) )
Hi,
All you need to do is drag Year to the filter/slicer and select 2017. Likewise, drag Month to the filter/slicer and select November.
You need to first calculate if the month is completed and then do MTD
ValueCalc = if(TODAY()>=EOMONTH(VALUES(Table1[DateRef]);0);EXPTRUE;EXPRFALSE)
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 |
---|---|
111 | |
94 | |
82 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |