Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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! 🙂
Proud to be a Super User!
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!
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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |