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
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Found the issue.  You need to use the Month Year column from your DimDate table in the table visual, not the one from the FactFinancial table.  Once I did that and cleared the slicers, it worked as expected.

 

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

Regards,

Pat

Anonymous's avatar
Anonymous
Not applicable
6 years ago

mahoneypat ,

I got what you said but this is not my requirement. I've tried this way. Sorry to say, we can't clear slicers since we need to show the last 12 months based on month-year selection. By adding Month Year from DimDate, if I select the single month from slicer it'll give only selected month data then we are losing the last 12-month purpose. It should show last 12 month data based on selected month year from slicer plus previous month data at each month level. 

 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft 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