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