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

Your pic shows that you are looking at the day granularity in your table.  I assumed you were looking at the month level.  Here are some new expressions that will work at the day level.

 

 

Running Total 12 M =
VAR __thisdate =
    MIN ( 'DimDate'[Date] )
RETURN
    CALCULATE (
        [Total Revenues],
        DATESINPERIOD ( 'DimDate'[Date], __thisdate, -12, MONTH )
    )

Running Total 12 M PM =
VAR __thisdate =
    MIN ( 'DimDate'[Date] )
RETURN
    CALCULATE (
        [Total Revenues],
        DATESINPERIOD ( 'DimDate'[Date], EDATE ( __thisdate, -1 ), -12, MONTH )
    )

 

 

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 ,

 

No success same results as of 12 months but now 0.00 comes on April 30, 2019.

 

 

Can u please download my attached power BI file and see where is the problem.

Link: https://www.dropbox.com/s/fy5olrgt7wgd9nq/Finance.pbix?dl=0


Let me clear u I've monthly fact data and I'm creating date table as a calculated table.

 

 

DimDate = 
ADDCOLUMNS (
CALENDAR (MIN(FactFinancial[Date]),MAX(FactFinancial[Date])),"DateYear", FORMAT ( [Date], "YYYY" ),"MonthYear",FORMAT([Date],"MMM YY"))

 

 Thanks

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    I wasn't able to open the pbix.  It prompted me for credentials.  As I don't have them (appropriately), it could not load the data model.  Is this a Direct Query model?

     

    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