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.
Greetings,
In advance I would like to apologise for my English. Also, I am quite a newbie to Pwoer BI and DAX, so whatever kind of errors you find in my way of thinking and programming are welcome!
The problem I have faced can be described as follows:
Imagine the following data table (call it DB):
Date Class Revenue
01/06/17 a 1
02/06/17 a 2
03/06/17 b 3
04/06/17 b 4
05/06/17 b 5
06/06/17 b 6
07/06/17 a 7
08/06/17 a 8
09/06/17 b 9
10/06/17 b 10
My goal is to calculate the difference in Revenue of both classes between DATE and DATE - 7 days. I know about the function DATEADD, and I could write something like:
Page Views Last 7 Days by Class a = IF(HASONEVALUE('DB'[Date]),
CALCULATE(SUM('DB'[Revenue]),
FILTER('DB','DB'[Class] = "a"),
DATEADD('DB'[Date],-7,DAY)
))
Then DIff.Rev Class a = IF(ISBLANK([Page Views Last 7 Days by Class a]), // If there is no -7 days momentum for a given date then return:
SUM('DB'[Revenue]),
SUM('DB'[Revenue]) - [Page Views Last 7 Days by Class a ]
IF we set it into a table with rows representing date we would get (REMEBER: we are filtering by class "a"):
Date Class DIff.Rev Class a
01/06/17 a 1 //remain the same since in our DB there is no -7 days register for class "a" with respect to this date
02/06/17 a 2 //remain the same since in our DB there is no -7 days register for class "a" with respect to this date
07/06/17 a 7 //remain the same since in our DB there is no -7 days register for class "a" with respect to this date
08/06/17 a 7 = 8-1 (08/06/17 - 01/06/17)
However, what I wanna see is:
Date Class DIff.Rev Class a
01/06/17 a 1
02/06/17 a 2
03/06/17 a (ex b) 0 //Asign Revenue = 0 since this momentum doesnt exist for class "a" and there is no - 7 days momentum
04/06/17 a (ex b) 0 //Asign Revenue = 0 since this momentum doesnt exist for class "a" and there is no - 7 days
05/06/17 a (ex b) 0 //Asign Revenue = 0 since this momentum doesnt exist for class "a" and there is no - 7 days
06/06/17 a (ex b) 0 //Asign Revenue = 0 since this momentum doesnt exist for class "a" and there is no - 7 days
07/06/17 a 7
08/06/17 a 7 = 8-1 (08/06/17 - 01/06/17)
09/06/17 a (ex b) -2=0-2 (09/06/17 - 02/06/17) //Asign Revenue = 0 since this momentum doesnt exist for class "a" and there is a - 7 days
10/06/17 a (ex b) 0 = 0-0 (10/06/17 - 03/06/17) //Asign Revenue = 0 since this momentum doesnt exist for class "a" and there is a - 7 days
Using words, I want to create a DAX funcion that evaluates the diff. Rev of class "a" for every date point in a way that, if some date point for class "a" doesn't exist then asign to its corresponding revenue the value of "0". Afterwards return this new "updated" envirounment of class a to the calculus of "DIff.Rev Class a".
But I have no Idea of how doing it, I've killed 2 days on it and achieved 0.
I rely on your knowledge and I hope you will help me to find a way out.
Kind Regards
Solved! Go to Solution.
Hi @PavelKonovalov,
My mistake!
In this scenario, you may need to create two separate tables to your mode(one for Date and another for Class) like below.
Date table
Date = CALENDARAUTO()
Class table
Class = DISTINCT(DB[Class])
And make sure there is no any relationships between the new added tables and the original DB table.
Then you can use the formulas below to create the measure, and use Class column from Class table, Date column from Date table as Slicers to get your expected result.
Page Views Last 7 Days = var currentDate = MAX('Date'[Date]) var currentSelectedClass = FIRSTNONBLANK(Class[Class],1) return CALCULATE ( SUM ( 'DB'[Revenue] ), FILTER(DB,DB[Class]=currentSelectedClass && DB[Date]=currentDate-7) )
DIff.Rev = var currentDate = MAX('Date'[Date]) var currentSelectedClass = FIRSTNONBLANK(Class[Class],1) return CALCULATE ( SUM ( 'DB'[Revenue] ), FILTER ( 'DB', 'DB'[Class] = currentSelectedClass && DB[Date]=currentDate ) ) - [Page Views Last 7 Days] + 0
Here is the modified pbix for your reference.
Regards
Hi @PavelKonovalov,
Based on my test, the formulas below should work in your scenario.
Page Views Last 7 Days by Class a = IF ( HASONEVALUE ( 'DB'[Date] ), CALCULATE ( SUM ( 'DB'[Revenue] ), FILTER ( ALL ( 'DB' ), 'DB'[Class] = "a" && DB[Date] = MAX ( DB[Date] ) - 7 ) ) )
DIff.Rev Class a = CALCULATE ( SUM ( 'DB'[Revenue] ), FILTER ( 'DB', 'DB'[Class] = "a" ) ) - [Page Views Last 7 Days by Class a] + 0
Here is the sample pbix file for your reference.
Regards
Greetings dear @v-ljerr-msft,
Thanks for your intent to help me. However, your approach doesnt solve the problem, since if you filter by class (let's say a) or date range you will see how the results appeared will correspond to the date range for which class a exists. This is what I wanna fix.
Best regards,
Hi @PavelKonovalov,
My mistake!
In this scenario, you may need to create two separate tables to your mode(one for Date and another for Class) like below.
Date table
Date = CALENDARAUTO()
Class table
Class = DISTINCT(DB[Class])
And make sure there is no any relationships between the new added tables and the original DB table.
Then you can use the formulas below to create the measure, and use Class column from Class table, Date column from Date table as Slicers to get your expected result.
Page Views Last 7 Days = var currentDate = MAX('Date'[Date]) var currentSelectedClass = FIRSTNONBLANK(Class[Class],1) return CALCULATE ( SUM ( 'DB'[Revenue] ), FILTER(DB,DB[Class]=currentSelectedClass && DB[Date]=currentDate-7) )
DIff.Rev = var currentDate = MAX('Date'[Date]) var currentSelectedClass = FIRSTNONBLANK(Class[Class],1) return CALCULATE ( SUM ( 'DB'[Revenue] ), FILTER ( 'DB', 'DB'[Class] = currentSelectedClass && DB[Date]=currentDate ) ) - [Page Views Last 7 Days] + 0
Here is the modified pbix for your reference.
Regards
@v-ljerr-msft Bravo, Sir!!! You've shown me how to grid in dax. I did what you did, but in R. I've had no idea how to do it in DAX until now. Thank you a lot!
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 |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |