Issue with Time Intelligence. Power BI Repoting

[PavelKonovalov]

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

[v-ljerr-msft]

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

[PavelKonovalov]

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,

[v-ljerr-msft · Accepted 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

[PavelKonovalov]

@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!