Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joshua1990
Post Prodigy
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:

DateFiscal MonthFiscal YearMinMax
01.01.2019Dec201803.12.201801.01.2019
02.01.2019Jan201902.01.201903.02.2019
03.01.2019Jan201902.01.201903.02.2019
....Jan201902.01.201903.02.2019
03.02.2019Jan201902.01.201903.02.2019
04.02.2019Feb201904.02.201901.03.2019

 

How would you manage that?

1 ACCEPTED SOLUTION
JarroVGIT
Resident Rockstar
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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
JarroVGIT
Resident Rockstar
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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors