Populating previous month values for multiple years in visual

[romals29]

Hi

I have a fact table that has daily HR data with a date field column, and the table is populated from July 2021 to June 2022 for 1 year of data.

I need to populate a pivot table visual showing the selected month value with the previous month's value.

I used the measure column below to create the previous month's value. 

Prev_Month Rcvd = CALCULATE(COUNTA('Main table'[ID]), PREVIOUSMONTH('Main table'[Date column].[Date]))

With this its working fine for year 2021 (july to dec), but 2022 (jan to june) showing blank values for previous month column.

I have created Date table also and created relationship with the Main table.

I couldn't find why its working fine for last year and not for current year. 

Please help me to fix this.

Thank You in advance

Romal

[parry2k]

@romals29 good it worked but it is not the correct approach.

[romals29]

Then can you help me to find. just want to know the best practice here..  Thank You

[romals29 · Accepted solution]

Hi 

I tried below and it worked out.

Here instead of Calendar.Date column, I have used Main Table.Date column in measure. 

Prior Month Count = CALCULATE(

    COUNTA(

        'Main Table'[ID]),

        DATEADD(

             'Main Table'[Date Column].[Date],

            -1,

            MONTH

        )

)

[parry2k]

@romals29 check my youtube video on such topic and that will get you the answer New Year and my MTD and QTD measures stopped working, why? - Power BI - YouTube

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[romals29]

Hi

I tried your solution that you used in MTD calculation to PREVIOUSMONTH calculation but it didn't work. 

Prev_Month Count = CALCULATE(COUNTA('Main table'[ID]), PREVIOUSMONTH( CALCULATETABLE( VALUES('Calendar'[Date]), KEEPFILTERS('Calendar'[Date]<= TODAY() ) ) ) )

Did I miss anything here.

Thank You