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.
Hi All,
I am building a report that needs to compare two time periods - either current month Vs previous month or previous month Vs two months ago.
I added to indication columns to my time dimesion - Is_Current_Month and Is_Previous_Month.
I also added a new table with two rows - one row for each of the possible comparison scenarions mentioned above (i added the scenario field as a page filter).
I want to filter my reports measure with one of the indication columns in the time table according to the user selction in the comparison scenarios filter.
I tried the following DAX for one of the measures but i get an erroe message saying that i can't use more than one column in the IF formula.
# Partners = CALCULATE(DISTINCTCOUNT(Volume[Partner_ID]),
IF(Scenarios[Scenario_ID]=1, TimeTable[Is_Current_Month], TimeTable[Is_Previous_Month]) = 1)
Any ideas as to how can i achive this?
Thanks in advance!!!
Solved! Go to Solution.
I don't think this is the best way to solve this problem. "Time shifting" or time intelligence is a standard feature of Power BI. You need a calendar table that follows "the rules". Read about calendar tables here.
http://exceleratorbi.com.au/power-pivot-calendar-tables/
Once you you have a valid calendar table, you can use functions like PREVIOUSMONTH
https://msdn.microsoft.com/en-us/library/ee634758.aspx
Advantages include
you don't need a column for every different time shift you need
your time shift can compare Feb with Jan and not just current month with previous month as of "today"
Thanks for your help!
I don't think this is the best way to solve this problem. "Time shifting" or time intelligence is a standard feature of Power BI. You need a calendar table that follows "the rules". Read about calendar tables here.
http://exceleratorbi.com.au/power-pivot-calendar-tables/
Once you you have a valid calendar table, you can use functions like PREVIOUSMONTH
https://msdn.microsoft.com/en-us/library/ee634758.aspx
Advantages include
you don't need a column for every different time shift you need
your time shift can compare Feb with Jan and not just current month with previous month as of "today"
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |