Time Intelligence With Custom Fiscal Calendar

DAX DAX Calculations
1

Hello,

I have a fiscal calendar that is not in line with the standard one. For example:

  • Period 01 contains days from 03/01/2021 until 06/02/2021
  • Period 02 contains days from 07/02/2021 until 06/03/2021

You can see these details in the Calendar table (FMTD_ID column).

How can I implement MTD/QTD/YTD functions that will calculate GSV based on this calendar?

For example, if I use MTD with this calendar, it will sum the sales until 31/01/2021. However, I would like MTD to be summing in line with the period (dates 03/01/2021 - 06/02/2021; FMTD_ID = 202101). Similiar for QTD, YTD.

Thank you for your help,
Marek

EDNA101.pbix (353.3 KB)

2

Ok I think I found the solution. :smile:

However I am happy to see if there are other solutions for this query.

GSV MTD =
VAR CurrentMonth = selectedvalue(‘Calendar’[FMTH_ID])
VAR CurrentDay = selectedvalue(‘Calendar’[DY_ID])
RETURN
calculate([GSV],
filter(all(‘Calendar’),
‘Calendar’[FMTH_ID]=CurrentMonth &&
‘Calendar’[DY_ID]<=CurrentDay))

3

Hi Marek,
I recently used Sam’s video on sorting quarter column and year column according to fiscal year.
It is really a helpful and detailed description given by Sam, I guess in the similar way, you can sort any column in you date table according to your need.
I am posting the link below, kindly have a look.
Hope it helps.

4

Hi @marek.regulski,

Your Date table seems to be missing some imporant attributes for these types of calculations. The basic pattern is as follows:

Custom MTD =
IF (
    HASONEVALUE( Dates[FSCL Year] ) && HASONEVALUE( Dates[FSCL Month Number] ),
    CALCULATE( [Your Measure],
        FILTER(
            ALL( Dates ),
            Dates[FSCL Year] = VALUES( Dates[FSCL Year] ) &&
            Dates[FSCL Month Number] = VALUES( Dates[FSCL Month Number] ) &&
            Dates[Date] <= MAX( Dates[Date] )
        )
    )
)

.
For Custom QTD just replace Dates[FSCL Month Number] by Dates[FSCL Quarter Number] and for Custom YTD just omit that second attribute Month/Quarter.

I hope this is helpful.

1 Like
5

Hi @marek.regulski , did the response provided by @Shumaila and @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

6

Yes, the answer from @Melissa is working. However, I still don’t really understand why this is different than the one I proposed (which is working as well). I will mark Melissa’s answer as the solution. Thank you.