Forum Discussion
Issue with Time Intelligence. Power BI Repoting
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
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] + 0Here is the modified pbix for your reference. :smileyhappy:
Regards
4 Replies
Hi PavelKonovalov,
Based on my test, the formulas below should work in your scenario. :smileyhappy:
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] + 0Here is the sample pbix file for your reference. :smileyhappy:
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] + 0Here is the modified pbix for your reference. :smileyhappy:
Regards
