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.
Hey guys! So I'm new around here and I really need some help.
Cenario:
- I have the costs of a company and they want me to make a comparison of costs, for example, between selected month and previous month (for that I used "PREVIOUSMONTH"). The problem is they also want a comparison between the selected month on the filter and month 12 from last year.
I've tried using "ALL" to make it independent of the date, however if the user filters december off of the filter, the value goes blank. Bear in mind that using another database only for december and making it the static doesn't work since when I do that, it undermines the filter for date and puts values for december for every single month.
What I'm actually looking for is the same usage of "PREVIOUS MONTH", but for a selected month, such as december.
Hope I was clear, english is not my first language. I'd be really greatful if someone was able to help me out.
Thanks!
Solved! Go to Solution.
Hi @ilyakoslov ,
I assume you are using a separate date table- if not please add one.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Then you can do something like this (adjust for your table and field names)
Measue for month 12 from last year =
VAR _month = 12
VAR _lastyear =
SELECTEDVALUE (Datetable[Year] )
RETURN
CALCULATE (
[Your Measure],
FILTER ( ALL ( Datetable ), Datetable[Month] = _month && Datetable[Year] = _lastyear )
)
Please accept as solution if this answers the question- thanks!
Hi @ilyakoslov ,
I assume you are using a separate date table- if not please add one.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Then you can do something like this (adjust for your table and field names)
Measue for month 12 from last year =
VAR _month = 12
VAR _lastyear =
SELECTEDVALUE (Datetable[Year] )
RETURN
CALCULATE (
[Your Measure],
FILTER ( ALL ( Datetable ), Datetable[Month] = _month && Datetable[Year] = _lastyear )
)
Please accept as solution if this answers the question- thanks!
Hey @djurecicK2 thank you SO MUCH, you were really fast and actually solved it. The only thing I had to change was that in your _lastyear variable you forgot to do it -1. Meaning that it must be from last year, not from the selected year. Anyway, small mistake. Thank you for the help!
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 |
---|---|
107 | |
97 | |
75 | |
63 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |