Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help in DAX

Hi Experts, I need help with DAX. I've date dimension (Date) and financial fact table (Date, Revenue) I calculating the last 12-month revenue based on month selection (Both slicers are single selec...
Need Help
Show and Tell
Tips and Tricks
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Ok.  I think I got it working.  Here is what I did.

     

    1.  Used the MonthYear column from your DimDate table in the slicer (optional but one click instead of two)

    2.  Added a MonthIndex column to your FactFinancial table (so I could easily do prev month calculation w/o Time Intelligence)

    MonthIndex = Year(FactFinancial[Date])*12+MONTH(FactFinancial[Date])
    3.  Made this Prev Revenue measure
    Prev Month Revenue =
    VAR maxmonthindex =
    MIN ( FactFinancial[MonthIndex] )
    RETURN
    CALCULATE (
    [Total Revenues],
    ALL ( DimDate ),
    ALL (
    FactFinancial[MonthYear],
    FactFinancial[MonthYearNo],  //needed since used as Sort By Column
    FactFinancial[MonthIndex]
    ),
    FactFinancial[MonthIndex] = maxmonthindex - 1
    )
     
    4.  Made these measures for Last 12 M and Last 12 M Prev Month
    Last 12 M =
    CALCULATE (
    [Total Revenues],
    DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
    )
     
    Last 12 M Prev Mon =
    CALCULATE (
    [Prev Month Revenue],
    DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
    )
     
    5.  Got this result
     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

Ok.  I think I got it working.  Here is what I did.

 

1.  Used the MonthYear column from your DimDate table in the slicer (optional but one click instead of two)

2.  Added a MonthIndex column to your FactFinancial table (so I could easily do prev month calculation w/o Time Intelligence)

MonthIndex = Year(FactFinancial[Date])*12+MONTH(FactFinancial[Date])
3.  Made this Prev Revenue measure
Prev Month Revenue =
VAR maxmonthindex =
MIN ( FactFinancial[MonthIndex] )
RETURN
CALCULATE (
[Total Revenues],
ALL ( DimDate ),
ALL (
FactFinancial[MonthYear],
FactFinancial[MonthYearNo],  //needed since used as Sort By Column
FactFinancial[MonthIndex]
),
FactFinancial[MonthIndex] = maxmonthindex - 1
)
 
4.  Made these measures for Last 12 M and Last 12 M Prev Month
Last 12 M =
CALCULATE (
[Total Revenues],
DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)
 
Last 12 M Prev Mon =
CALCULATE (
[Prev Month Revenue],
DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)
 
5.  Got this result
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat