cancel
Showing results for
Did you mean:  Post Prodigy

## Calculated Column with Max and Min Date for Fiscal Months

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?

1 ACCEPTED SOLUTION  Resident Rockstar

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! 🙂

Proud to be a Super User!

2 REPLIES 2  Resident Rockstar

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! 🙂

Proud to be a Super User! Anonymous
Not applicable

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   