Month to date on custom date

DAX DAX Calculations
1

Hi

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.

The desired result for this data set would be
image

Cycle to date example.pbix (472.0 KB)

Thanx

2

@annamarie,

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.

Thanks!

– Brian

3

Thanx Brian, I’ve added a file now.

4

@annamarie,

Perfect – will have a solution back to you shortly.

  • Brian
5

@annamarie,

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?)

image
image568×501 52.5 KB

Thanks.

  • Brian

My current solution file attached here:
eDNA Forum - Cycle to date example solution.pbix (452.3 KB)

PS – when you’re doing time intelligence calculations, be sure to mark your Dates table as a date table.

6

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

7

@annamarie,

Thanks very much for the clarification, should have a solution back to you later tonight.

  • Brian
1 Like
8

@annamarie,

Wow! I can totally see why this one gave you so much trouble. On the face of it, it looks pretty simple but proved very recalcitrant to crack.

So, good news and bad news. The good news is I have a working solution that seems to be producing all the expected results:

image
image689×515 25.9 KB

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.

So, I hope this is helpful but more to follow…

– Brian
eDNA Forum - Cycle to date example solution.pbix (480.5 KB)

1 Like
9

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

10

@annamarie,

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.

– Brian

11

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

12

@annamarie,

Happy to help. I ended up learning a lot on this one as well.

Good luck with your meeting on Monday.

– Brian