Forum Discussion

PavelKonovalov's avatar
PavelKonovalov
Frequent Visitor
9 years ago
Solved

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 welco...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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]
    + 0

 

Here is the sample pbix file for your reference. :smileyhappy:

 

Regards

  • PavelKonovalov's avatar
    PavelKonovalov
    Frequent Visitor
    9 years ago

    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's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      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. :smileyhappy:

       

      Regards

      • PavelKonovalov's avatar
        PavelKonovalov
        Frequent Visitor
        9 years ago

        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!