Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am sharing my Pbix file here.
I am trying to apply filter on month to display data of (1) current month, (2) 1 month prior, (3) 2 month prior etc.
So, for example (today is 10/7/2021), if we are talking about 1 month prior, it would be 9/1 - 9/30, not relative dates like 9/6 - 10/6.
I looked online for example:
The closest that I found was using a measure like this:
last_month_data =
var current_month= MONTH(TODAY())
return
CALCULATE(SUM('Table1'[Data]),
FILTER(
'Table1',
MONTH('Table1'[Month]) = current_month -1
)
)
On my Pbx file, date field is called "IncidentDate".
Bottom is screenshot of my visuals:
On these visuals, I am using relative date like below. but that is not what I want.
For 2 month prior, it should be 8/1/2021 - 8/31/2021.
How do I apply the logic into filter?
I guess I am stuck here:
How do I apply measure [Falls Count] into sum?
OR..
It appears that second screenshot (measure) seems to be working (I need to double check), but I am curious why I have to break down the measure "Falls Count" into this
to make it work..
Thanks.
Solved! Go to Solution.
You can just add this column to your Dates table to get the relative months from Today, and then add that column to your visual with is -1 and is -2.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I have added my Date table - I recommend deleting your Dates table to avoid confusion of too many tables. You need to make sure to mark as date table, whichever option you choose: https://excelwithallison.blogspot.com/search?q=dimdate
I also created a DimHour table to simplify a bit, this means you don't need to create those calculated columns in the Fact table.
Here's what it looks like now:
Measure pattern is:
But that won't work for 2 month prior as it will include both 1 and 2 months prior, thus the need for the DAX I gave above.
See attached updated file below signature. 😀
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
I have added my Date table - I recommend deleting your Dates table to avoid confusion of too many tables. You need to make sure to mark as date table, whichever option you choose: https://excelwithallison.blogspot.com/search?q=dimdate
I also created a DimHour table to simplify a bit, this means you don't need to create those calculated columns in the Fact table.
Here's what it looks like now:
Measure pattern is:
But that won't work for 2 month prior as it will include both 1 and 2 months prior, thus the need for the DAX I gave above.
See attached updated file below signature. 😀
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
I have a question. I see that you included a slicer that selects Year and Month. I am trying to make the current month as a default value (more dynamic) instead of choosing the value from this filter. How do I that? I guess I do not need this filter at all. Thanks.
@JustinDoh1 wrote:
I have a question. I see that you included a slicer that selects Year and Month. I am trying to make the current month as a default value (more dynamic) instead of choosing the value from this filter. How do I that? I guess I do not need this filter at all. Thanks.
Correct, you could use Relative Date slicer on the date column and choose this month - that will be dynamic, or use Page/All Pages level filter in the filter pane.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
You can just add this column to your Dates table to get the relative months from Today, and then add that column to your visual with is -1 and is -2.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat I think your solution totally makes sense to me. Going back to my struggle with coming up with measure, is there any solution? Thanks.
You should be able to just use a measure expression like this. Is that what you mean?
NewMeasure =
VAR vThisMonthIndex =
MIN ( Date[Months From Today] )
RETURN
CALCULATE (
[YourMeasure],
ALL ( Date ),
Date[Months From Today] = vThisMonthIndex - 1
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |