DAX measure to count months in current FY

DAX DAX Calculations
1

Good day all

Need some guidance on a measure i need to create. i need to calculate count of months in currently FY.

Fiscal Year = december to November

if we calculate amount of months december 2020 till today (10 & half month)

2

Hello @neilonbooysen,

Thank You for posting your query onto the Forum.

To achieve the results as per the condition that you’ve specified. Below is the measure alongwith the screenshot of the final results - 1 provided for the reference -

Current Fiscal Months Calculation - 1 = 
VAR _Month_Calculation = 
DATEDIFF(
    DATE( YEAR( TODAY() ) - 1 , 12 , 1 ) ,
    TODAY() ,
    MONTH )

VAR _Days_Calculation = 
DATEDIFF(
    DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
    TODAY() ,
    DAY )

VAR _Condition = 
SWITCH( TRUE() ,
    _Days_Calculation > 20 && _Days_Calculation <= 31 , "1/3 Month Remaining" , 
    _Days_Calculation > 10 && _Days_Calculation <= 20 , "1/2 Month" , 
    _Days_Calculation < 10 , "2/3 Month Remaining" , 
0 )

VAR _Results = 
_Month_Calculation & " &" & " " & _Condition

RETURN
_Results

Final Results - 1

Alternatively, if you want analyze the results something like “Months and Days” you can create a measure like this. Below is the measure alongwith the screenshot of the final results - 2 provided for the reference -

Current Fiscal Months Calculation - 2 = 
VAR _Month_Calculation = 
DATEDIFF(
    DATE( YEAR( TODAY() ) - 1 , 12 , 1 ) ,
    TODAY() ,
    MONTH )

VAR _Days_Calculation = 
DATEDIFF(
    DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
    TODAY() ,
    DAY )

VAR _Results = 
_Month_Calculation & " Months" & " &" & _Days_Calculation & " Days"

RETURN
_Results

Final Results - 2

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Important Note: Please modify the conditions under the “Measure - 1” as per your additional requirements.

Thanks and Warm Regards,
Harsh

Fiscal Months - Harsh.pbix (656.3 KB)

1 Like
3

thanks so much, this works perfectly

4

Hello @neilonbooysen,

You’re Welcome. :slightly_smiling_face:

I’m glad to hear that it worked for you and you found it helpful.

Thanks and Warm Regards,
Harsh