I hope someone can help me with a bit of DAX. The report I’m doing has to report on cycle dates, the cycle starts on the 5th of the month and ends on the 4th. I have successfully added the cycle on my DAX calendar like this.
“CycleEndDate”, IF(DAY([Date]) <=4,date(YEAR([Date]),MONTH([Date]),4),DATE(YEAR([Date]),MONTH([Date])+1,4))
I now need to do Sales for Cycle to date. And compare to Same Period Last Month and Same Period Last Year. I’ve tried a couple of ways but not been able to crack it. Hope someone can help.
For these sorts of questions, it’s a big help (and you’ll get the best and fastest response) if you can please post a PBIX file and a mockup of the results you want to see.
My DAX is giving me exactly the answers I would expect, but they don’t match your expected mockup results. What am I missing in terms of understanding of your problem? (I.e., how did you generate those mockup figures for the two that don’t match mine?)
Hi Brian
Sorry, I should have been more specific. My calculation is for the Same period the previous month meaning also a “partial” cycle in this instance, so from the 5th - 17th of the month, and not the full previous cycle which is what you results are showing.
Thanx
The bad news is I had to wrestle this problem into submission with some complex virtual table work that frankly I don’t understand why it was necessary:
Curr Per LM Dates =
VAR vTable =
FILTER (
VALUES ( Dates[Date] ),
Dates[Date] >= MIN ( 'Current Per Dates'[Date] )
&& Dates[Date] <= MAX ( 'Current Per Dates'[Date] )
)
VAR Dur =
COUNTROWS ( vTable )
VAR DAvTable =
DATEADD ( vTable, -1, MONTH )
VAR BottomN =
ADDCOLUMNS (
DAvTable,
"@Rank", RANKX ( DAvTable, Dates[Date],, ASC ),
"@TotSales", [Total sales]
)
VAR Result =
FILTER ( BottomN, [@Rank] <= Dur )
RETURN
Result
I expected that the DAvTable virtual table would have returned the correct range of dates, without the subsequent RANKX filtering needed. I’m going to request that another member of the expert team review this, and explain the unexpected behavior of DATEADD in that variable in order that I have full confidence in the solution provided below.
Thank you so much for the help so far. You’ve gotten further than me. I really struggled with getting this right. I look forward to further feedback.
A
Okay, I stepped away for a bit and looking at my solution fresh, I now understand why DAvTable behaves as it does, and I believe that the solution provided above is correct.
Perfect, thank you so much for the comprehensive solution as well as the quick replies. I really appreciate it. I will incorporate this into my actual report, I’m sure it will work. Ready for a very big meeting on Monday.
A