Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Type | Prev Month Count |
News | 500 |
Reminders | 267 |
Good to know | 900 |
Like above table it must show
I have a relationship between dim table and fact table with ID
Please help
Thanks in advance!!!
Hello @Bharathi_99,
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.
Hello @Bharathi_99,
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.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |