Forum Discussion

mosman's avatar
mosman
Frequent Visitor
6 years ago
Solved

DAX Previous Month and Filtering

Hello all,   I have table with sales and dates, the table is an aggregate table, it calculate the total sales for the month end. it looks like this: Product_Category   Sales Amount   Date Bikes ...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi mosman 

    Could you create a column that gets the max date for each month in your date table? Then create a helper table that is linked to your date table, with only the distinct values of that column?

     


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

mosman's avatar
mosman
Frequent Visitor
6 years ago

the measure looks like this:

Starting Balance Amount = CALCULATE(SUM('FactSales'[currentBalance]),ALL(DimDate),PARALLELPERIOD(DimDate[Date],-1,MONTH))
mosman's avatar
mosman
Frequent Visitor
6 years ago

when I use the date from the time intelligence table in the slicer, it looks like this:

but I want it to look like this:

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi mosman ,

    Might you create a column in the Date table that gives you the max date for each month and use that for your slicer?
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel