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.
Hello Power BI Community,
I have a problem with comparing the data from the actual month with the previous month. For my managment revenue-report I want to show to tables with the revenue figures of the acutal and the previous month, seperated into 4 Weeks. The table should look like the following one:
Region | 1. Week | 2. Week | 3. Week | 4. Week |
Germany | ### | ### | ||
East Europe | ### | |||
West Europe | ||||
North America |
What I did so far was creating two measures:
And they both work out very well. To seperate the revenue into 4 weeks I created a date table, where all the dates are assigned to a week number, so 1st-7th = 1. Week, 8th-14th = 2. Week and so on.
Than I creates a matrix visiual with regions in the rows, weeks from the date table in the columns and the two measures provide the data. This concept works out very well form the data of the actual month, but for some reason it doesn't work for the previous month matrix. In fact my two tables look like this:
Actual Month
Region | 1. Week | 2. Week | 3. Week | 4. Week | Sum |
Germany | 25 | 25 | 25 | 25 | 100 |
East Europe | 25 | 25 | 25 | 25 | 100 |
West Europe | 25 | 25 | 25 | 25 | 100 |
North America | 25 | 25 | 25 | 25 | 100 |
Sum | 100 | 100 | 100 | 100 | 400 |
Previous Month
Region | 1. Week | 2. Week | 3. Week | 4. Week | Sum |
Germany | 100 | 100 | 100 | 100 | 100 |
East Europe | 100 | 100 | 100 | 100 | 100 |
West Europe | 100 | 100 | 100 | 100 | 100 |
North America | 100 | 100 | 100 | 100 | 100 |
Sum | 400 | 400 | 400 | 400 | 400 |
So I am asking for your support, helping me to solve the problem or coming up with another interesting idea to achieve my goals.
Thank you!
Best regards
Julius
*Unfortunatly I am not able to upload pictures, so I hope my table examples above show were the problem could be. If you have any further questions just ask me.
Solved! Go to Solution.
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
@Anonymous Hi Julius,
May I ask a quesiton... I'd also like to seperate my data into 4 weeks each month while I am not sure how to assigne week number like what you have done (1st-7th = 1. Week, 8th-14th = 2). Right now I am using WEEKNUM with the fomula:
1 + WEEKNUM (Index[Date])-WEEKNUM(STARTOFMONTH (Index[Date]))
but it sets week based on weekday, so for example 2021/02/01 is a monday, than only 2021/02/01-2021/02/06 will be set as week 1, but what I want is 1st-7th every month being set as week 1... Could you please kindly share how you did it?
Many thanks!
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
Hey @AllisonKennedy ,
thank you for your fast support.
My date table looks like this:
Its in german, I hope your are not confused. And yes there is a slicer on the page.
@Anonymous , the previous month should work. can you share formula?
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
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
Hey @AllisonKennedy ,
thank you for the solution. The formula does work perfectly. In fact I tried it before and it didn't work, maybe because the date table was not declared as a date table. With adjusting the settings of the date table and rewriting the dax formula with DATEADD it now works.
Thanks to everybody for your support!
Best regards
Julius
Have you marked your date table as "Date Table"
Proud to be a Super User!
@Anonymous , what is the formula used for previous month?
Can you share sample data and sample output in table format?
refer
Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD Questions— Time Intelligence 4–5
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |