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 have a large table that contains sales $s by store number on a daily basis (1 row per store per day). I'm trying to create a measure that will compare the total sales for each store against the average of the two prior months - however, I only want the measure/sum to include store numbers that are present in each of the 3 months (current month & two prior months being used for the baseline).
So - Jan total $s sold total will be compared to average of Nov & Dec $s total - but only store numbers that are present in the sales data in Nov, Dec & Jan should be included in the totals...
Thank you in advance!
Hi @Anonymous ,
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Miguel,
The part I'm struggling with is how to identify the common stores across the 3 month range - lets say I have something like this (I'm able to do a distinct for each month to get the strings/values):
Jan = {"A", "B", "C", "D"}
Feb = {"B", "C", "D"}
Mar = {"C", "D", "F", "G"}
How can I write DAX to get the answer:
Common = {"C", "D"}
Result should be an array as there will likely be hundreds of values/store codes that I will then use as a filter to get the sales for those stores.
Thanks!
Hi @Anonymous ,
Depending on the way you have the model. Do you have any calendar table?
Asking this because you need to make an intersect between the 3 months values, but to do the filter of the 3 months, But depending on how you have the model there may need to be some adjustments made.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
108 | |
106 | |
87 | |
77 | |
69 |
User | Count |
---|---|
124 | |
112 | |
94 | |
84 | |
75 |