Forum Discussion

Metacomet's avatar
Metacomet
Regular Visitor
1 year ago
Solved

Calculating data for previous month with selected date

Hi,
I have two columns: PRODUCT and DATE.
On a canvas, there is a slicer, where I can choose any month and year for the last three years.
Also, there are two cards:
- The first one is calculating all products for the selected month,
- The second one should calculate all products for the previous months.

 

Without a slicer, this code works perfectly:

Products Previous Month =
CALCULATE(
   DISTINCTCOUNT(
        'Table'[PRODUCT]),
        FILTER('Table', PREVIOUSMONTH('Table'[DATE])
        )
)

But I can't get it to work with the slicer. Whenever I choose a month, I get the same number on both cards.

I have searched for answers and found some solutions and hints, but for some reason, all calculations return the same amount for the selected and previous month.
Here is an example of one of the solutions I found:

Products Previous Month =
VAR _SelectedMonth = SELECTEDVALUE('Table'[DATE])
VAR _diff = DATEDIFF(_SelectedMonth, TODAY(), MONTH) +1
RETURN
CALCULATE(
   DISTINCTCOUNT(
        'Table'[PRODUCT]),
        FILTER('Table', DATEADD('Table'[DATE], -_diff, MONTH)
        )
)



  • Metacomet's avatar
    Metacomet
    1 year ago

    I have found a solution and I'm sharing it if anyone else needs it in the future:

    VAR _PreviousMonthDate = MONTH(PREVIOUSMONTH('TABLE'[DATE]))
VAR _PreviousYearDate = IF(_PreviousMonthDate = 12, YEAR(SELECTEDVALUE('TABLE'[DATE]))-1, YEAR(SELECTEDVALUE('TABLE'[DATE])))
RETURN
CALCULATE(
    DISTINCTCOUNT('TABLE'[PRODUCT]),
        FILTER(
            ALL('TABLE'[DATE]),
                YEAR('TABLE'[DATE]) = _PreviousYearDate
                &&
                MONTH('TABLE'[DATE] = _PreviousMonthDate
            )

)

     

    However, while trying many, many solutions, I broke somehow (no idea how) hierarchy in the [DATE] column, and suddenly the first calculation I created started to work:

    CALCULATE(
    DISTINCTCOUNT('TABLE'[PRODUCT]), PREVIOUSMONTH('TABLE'[DATE])
)

    But I can't have a broken hierarchy, so I had to revert it.

    As I would like to understand this, does anyone know why this is creating problems for the calculations?

8 Replies

  • Metacomet's avatar
    Metacomet
    Regular Visitor
    1 year ago

    I have found a solution and I'm sharing it if anyone else needs it in the future:

    VAR _PreviousMonthDate = MONTH(PREVIOUSMONTH('TABLE'[DATE]))
VAR _PreviousYearDate = IF(_PreviousMonthDate = 12, YEAR(SELECTEDVALUE('TABLE'[DATE]))-1, YEAR(SELECTEDVALUE('TABLE'[DATE])))
RETURN
CALCULATE(
    DISTINCTCOUNT('TABLE'[PRODUCT]),
        FILTER(
            ALL('TABLE'[DATE]),
                YEAR('TABLE'[DATE]) = _PreviousYearDate
                &&
                MONTH('TABLE'[DATE] = _PreviousMonthDate
            )

)

     

    However, while trying many, many solutions, I broke somehow (no idea how) hierarchy in the [DATE] column, and suddenly the first calculation I created started to work:

    CALCULATE(
    DISTINCTCOUNT('TABLE'[PRODUCT]), PREVIOUSMONTH('TABLE'[DATE])
)

    But I can't have a broken hierarchy, so I had to revert it.

    As I would like to understand this, does anyone know why this is creating problems for the calculations?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Metacomet ,

      Is this issue solved now? If you have any questions, please feel free to share with us.

      Could you please provide example data or sample files here if you have any confused? We could offer you more help if we have information in detail. And what you expect the output to be. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

      Best regards,

      Lucy Chen

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    1 year ago

    Hi Metacomet ,

    Try this

    Products Previous Month =
CALCULATE(
   DISTINCTCOUNT(
        'Table'[PRODUCT]),
        FILTER(all('Table'), PREVIOUSMONTH('Table'[DATE])
        )
)
    • Metacomet's avatar
      Metacomet
      Regular Visitor
      1 year ago

      Thank you for your reply. Unfortunately, this solution always returns the same value, no matter what month I choose.

  • moncx's avatar
    moncx
    Icon for Resolver II rankResolver II
    1 year ago

    Hey,

    create separate Date table in your model, because if dates in your fact table are not full then PREVIOUSMONTH functions gets confused and does not return correct answer. You can create Date table with DAX like this:

    Date =
    VAR MinYear = YEAR ( MIN ( 'Table'[DATE] ) )
    VAR MaxYear = YEAR ( MAX ( 'Table'[DATE] ) )
    RETURN
    ADDCOLUMNS (
        FILTER (
            CALENDARAUTO( ),
            AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
        ),
        "Year", YEAR ( [Date] ),
        "Month Name", FORMAT ( [Date], "mmmm" ),
        "Month Number", MONTH ( [Date] )
    )

    Then connect in your model Date table field [Date] with your Table field [Date] and use Date table in your slicer and measure for calculating previous month. For previous month DAX looks like this:
    Products Previous Month =CALCULATE([Products Current Month], PREVIOUSMONTH('Date'[Date]))

    It is always better to create Date table when you do calendar/date calculations, because the functions work best then because you have table with all the dates and calculations can be performed correctly then.
    • Metacomet's avatar
      Metacomet
      Regular Visitor
      1 year ago

      Thank you for your advice. I tried this solution, but it always returns BLANKs.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Metacomet ,

    Please follow the steps below.

    1. Create a Date Table, add new columns to calculate the years and months in the table.

     

    2. Manage a new relationship.

    3. Create the first measure to calculate all products for the selected month.

    Selected Month Products = 
CALCULATE(
    DISTINCTCOUNT(
        'Table'[Product]),
        ALLSELECTED(
            'Date'[Year],'Date'[Month])
            )

    4. Create the second measure to calculate all products for the previous months.

    Products Previous Month = 
Var _minmonth=CALCULATE(MIN('Date'[Month]),ALLSELECTED('Date'[Month]))
RETURN
CALCULATE(
    DISTINCTCOUNT(
        'Table'[Product]),
        FILTER(
            ALL('Table'),
            [Date]<=EOMONTH(DATE(SELECTEDVALUE('Date'[Year]),_minmonth,1),-1)
            )
)

    Best regards,

    Lucy Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Metacomet's avatar
      Metacomet
      Regular Visitor
      1 year ago

      Thank you for your answer. I tried to apply it to my data, but I have a many-to-one relationship, and this is not working with your solution. I downloaded your solution and changed its relationship to many-to-one, and it stopped working as well. Sorry.