The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
On my page, I have sales in 2019 and a slicer filter with months.
Supposing that user selects February, April and June.
I need to create a % of Growth:
- Between February and Nothing (Because we don't have anything before).
- Between April and February (Because February is the previous month among selected months).
- Between June and April (Because April is the previous month among selected months).
Note that I do not want the previous month direct and yes among the selected ones.
First things first. I can create a % of Growth using PREVIOUSMONTH or PREVIOUSYEAR or SAMEPERIODLASTYEAR, but this case is more dynamic. If someone knows how to select previous period among the selected ones, I think I can create my % of Growth on my own.
To be more clear, I need something like this:
Month | SalesOnMonth | PreviousMonthAmongSelected | SalesOnPreviousMonthAmongSelected | % of Growth |
feb/2019 | 50 | |||
apr/2019 | 200 | feb/2019 | 50 | 300% |
jun/19 | 1000 | apr/2019 | 200 | 400% |
Solved! Go to Solution.
Hi @gluizqueiroz ,
I'm assuming you have a calendar table, you need to also have a endo of month column on the sales and on the calendar table (this is because I don't know how you have your model setup)
Create the following measure:
Previous = VAR Date_Selection = CALCULATE ( MAX ( Sales[EndofMonth] ); FILTER ( ALLSELECTED ( 'Calendar' ); 'Calendar'[EndOfMonth] < MAX ( 'Calendar'[EndOfMonth] ) ) ) RETURN IFERROR ( ( SUM ( Sales[Sales] ) - CALCULATE ( SUM ( Sales[Sales] ); FILTER ( ALL ( 'Calendar'[EndOfMonth] ); 'Calendar'[EndOfMonth] = Date_Selection ) ) ) / CALCULATE ( SUM ( Sales[Sales] ); FILTER ( ALL ( 'Calendar'[EndOfMonth] ); 'Calendar'[EndOfMonth] = Date_Selection ) ); 0 )
Check PBIX file attach.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @gluizqueiroz ,
I'm assuming you have a calendar table, you need to also have a endo of month column on the sales and on the calendar table (this is because I don't know how you have your model setup)
Create the following measure:
Previous = VAR Date_Selection = CALCULATE ( MAX ( Sales[EndofMonth] ); FILTER ( ALLSELECTED ( 'Calendar' ); 'Calendar'[EndOfMonth] < MAX ( 'Calendar'[EndOfMonth] ) ) ) RETURN IFERROR ( ( SUM ( Sales[Sales] ) - CALCULATE ( SUM ( Sales[Sales] ); FILTER ( ALL ( 'Calendar'[EndOfMonth] ); 'Calendar'[EndOfMonth] = Date_Selection ) ) ) / CALCULATE ( SUM ( Sales[Sales] ); FILTER ( ALL ( 'Calendar'[EndOfMonth] ); 'Calendar'[EndOfMonth] = Date_Selection ) ); 0 )
Check PBIX file attach.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey @MFelix.
Thanks for helping me Mr. Felix!
I adapt your solution to my case and it works nice!
I didn't need to use End Of Month, because I can't. I have year, quarter and month in my matrix and it create a drill, so I need to make this dynamic and your solution guide me to achieve my goal.
Thanks thanks!
Hi @gluizqueiroz ,
Glad I could help, since you didn't refer anything I just did a simple table as you add in your example.
No need for the Mr. :):)
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsJoin the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
94 | |
86 | |
70 | |
65 |
User | Count |
---|---|
244 | |
127 | |
119 | |
81 | |
78 |