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...
  • 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?

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.