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

Assuming your DimDate table is Marked As Date Table, you can use Time Intelligence functions to do this.  Here are example measures for your 12 Month rolling total, and the 12 Month rolling total starting from the previous month.

 

Running Total 12 M =
CALCULATE (
    [Total Sales],
    DATESINPERIOD (
        'Date'[Date],
        MAX ( 'Date'[Date] ),
        -12,
        MONTH
    )
)


Running Total 12 M PM =
CALCULATE (
    [Total Sales],
    DATESINPERIOD (
        'Date'[Date],
        EDATE ( MAX ( 'Date'[Date] ), -1 ),
        -12,
        MONTH
    )
)

 

 

FYI that I posted a blog recently with a useful "Sandbox" file to practice doing Time Intelligence functions, and used a MinMaxDates measure to confirm I had the right date ranges for the above.  You can see it here, if interested.

https://community.powerbi.com/t5/Community-Blog/A-Self-Contained-quot-Sandbox-quot-PBIX-File-to-Practice-With/ba-p/1161256

 

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

Hi mahoneypat ,

 

I've tried your given DAX you can see the output is attached in question to keep track of what I've tried. Both Dax giving same output.

I've also attached the PBIX file for your reference.

Thanks

  • 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