Calculated Column with Max and Min Date for Fiscal Months

[joshua1990]

Hello everybody!

I am looking for a function (Dax) for a calculated column within my calendar table that shows me the Min and Max (Start and End Date) per Fiscal Month (Without any Power Query).

I have the following structure in my calendar table:

Date	Fiscal Month	Fiscal Year	Min	Max
01.01.2019	Dec	2018	03.12.2018	01.01.2019
02.01.2019	Jan	2019	02.01.2019	03.02.2019
03.01.2019	Jan	2019	02.01.2019	03.02.2019
....	Jan	2019	02.01.2019	03.02.2019
03.02.2019	Jan	2019	02.01.2019	03.02.2019
04.02.2019	Feb	2019	04.02.2019	01.03.2019

How would you manage that?

[JarroVGIT · Accepted solution]

Something like this (assuming you have the first three columns):

MIN:

Min = 
VAR curFiscalMonth = DateTable[Fiscal Month]
VAR curFiscalYear = DateTable[Fiscal Year]
CALCULATE(MIN(DateTable[Date]), FILTER(ALL(DateTable), DateTable[Fiscal Month] = curFiscalMonth && DateTable[Fiscal Year] = curFiscalYear))

MAX:

Min = 
VAR curFiscalMonth = DateTable[Fiscal Month]
VAR curFiscalYear = DateTable[Fiscal Year]
CALCULATE(MAX(DateTable[Date]), FILTER(ALL(DateTable), DateTable[Fiscal Month] = curFiscalMonth && DateTable[Fiscal Year] = curFiscalYear))

 Let me know if this works!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

@JarroVGIT 

Thanks a lot for this wonderful piece of Dax. It solves my Fiscal calendar problem which i was trying to achieve with granularity mentioned in this video. https://www.youtube.com/watch?v=hilfglpKNRQ