Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Dynamic previous month data

Hi ,

I need to create a dynamic previous month showing data in power bi

I have date column in Fact table as "date"
I also have views col as "views" in Fact table

I have type name col in Dim table 

If I select particular month from date filter it must show count(views) for previous month of selected month for the Type= News, Reminders and Good to know in my power bi
For example if I select Sep-2022 it must show Aug - 2022 count of views for Type News, Reminders and Good to know 

It must show count for News , Reminders and Good to Know separately

TypePrev Month Count
News500
Reminders267
Good to know900


Like above table it must show

I have a relationship between dim table and fact table with ID

Please help

Thanks in advance!!!

Need Help
Tips and Tricks
Tutorial Requests

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    Hello Anonymous,

     

    Use the following DAX formula to calculate the count of views for the previous month:

    Prev Month Count = 
VAR SelectedMonth = SELECTEDVALUE('Fact table'[date])
VAR PreviousMonth = EDATE(SelectedMonth, -1)
RETURN
CALCULATE(
    COUNT('Fact table'[views]),
    'Fact table'[date] = PreviousMonth,
    'Dim table'[Type] IN {"News", "Reminders", "Good to know"}
)

    Let me know if you might need further assistance.

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    Hello Anonymous,

     

    Use the following DAX formula to calculate the count of views for the previous month:

    Prev Month Count = 
VAR SelectedMonth = SELECTEDVALUE('Fact table'[date])
VAR PreviousMonth = EDATE(SelectedMonth, -1)
RETURN
CALCULATE(
    COUNT('Fact table'[views]),
    'Fact table'[date] = PreviousMonth,
    'Dim table'[Type] IN {"News", "Reminders", "Good to know"}
)

    Let me know if you might need further assistance.