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,
I have a Data table(Main), Calendar Table (Month), and Type table (Type). I use two slicers; one for type selection from the Type table and the other one is for the month selection from the Month table.
I want to calculate the sum for the selected type with the condition: If 02.2020 is selected and type b is selected, it should bring the sum between 12.2019 (inclusive) and 10.2019 (inclusive). Similarly; if I select 05.2020, the sum should be between 03.2020 and 01.2020.
I wrote this measure and it brings data starting from the selection month to 2 months backward which I don't want:
I have also the list to correspond to the result, but it is also not in the way that I am looking for. I uploaded the file to:
https://www.dropbox.com/s/t9mbm0x1p3l1eti/sum%20between2.pbix?dl=0
Can you help me how to show data between -2 and -5 months based on the selection?
Thanks a lot in advance!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi, thanks for the measure. it works. Is it possible to see the values as a table? For example; if I select 02.2020 and type "b", I want to see the list:
b | 12.2019 | 5 |
b | 11.2019 | 2 |
b | 10.2019 | 4 |
thanks in advance
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you very much! How can I show those in a table as a list? regards,
Hi,
Not sure of how to do that.
Hi, I used the measure in a line chart to show the values for each month and it didn't work. Do I do stmh wrong? Regards,
Hi,
My measure will return a single scalar value. That is what i thought your original requirement was. Now i do not know what you want.
@IF Is your month table a date table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Is your Type table related to Main table? It should be, so you don't need this filter included in your formula. https://excelwithallison.blogspot.com/search?q=complicated
If so, try using DAX DATESINPERIOD https://dax.guide/datesinperiod/ :
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
Hi, if I am doing it correctly, the measure doesn't show the value in a line chart to show values for each month. line. Can you test as well? Thanks 🙂
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |