Today()-1 vs Today()-2

Hi all, I need to display in a column graph data from 2 days and yesterday. So the chart will easilly show the 2 days trend. I used this in Power BI desktop and it works well but when gets time...

Need Help

v-chuncz-msft

9 years ago

elatreille,

You could also create measures as shown below.

Yesterday =
SUMX (
    FILTER ( AdvRev, AdvRev[Date] = MAX ( AdvRev[Date] ) - 1 ),
    AdvRev[Total impressions]
)

Two_Days =
SUMX (
    FILTER ( AdvRev, AdvRev[Date] = MAX ( AdvRev[Date] ) - 2 ),
    AdvRev[Total impressions]
)

elatreille

Helper I

9 years ago

OK, I found a way to do so but this is a kind of patch... I am pretty sure there is a way to do it better so I will continue my research on it. Here is what I did:

Yesterday = SUM(AdvRev[Total impressions]) - CALCULATE(SUM('AdvRev'[Total impressions]),DATEADD('AdvRev'[Date],-1, DAY))

Where: SUM(AdvRev[Total impressions]) returns the total impressions since day one and CALCULATE(SUM('AdvRev'[Total impressions]),DATEADD('AdvRev'[Date],-1, DAY)) returns the total impressions before yesterday.

So SUM(AdvRev[Total impressions]) - CALCULATE(SUM('AdvRev'[Total impressions]),DATEADD('AdvRev'[Date],-1, DAY)) = the impressions served Yesterday only

Two_Days = SUM(AdvRev[Total impressions]) - (CALCULATE(SUM('AdvRev'[Total impressions]),DATEADD('AdvRev'[Date],-2, DAY)) + 'AdvRev'[Yesterday])

I did the same calculation from first day to 2 days before and added the yesterday impressions...

Let me know if someone has a better solution ;)

v-chuncz-msft

Community Support

9 years ago

elatreille,

You could also create measures as shown below.

Yesterday =
SUMX (
    FILTER ( AdvRev, AdvRev[Date] = MAX ( AdvRev[Date] ) - 1 ),
    AdvRev[Total impressions]
)

Two_Days =
SUMX (
    FILTER ( AdvRev, AdvRev[Date] = MAX ( AdvRev[Date] ) - 2 ),
    AdvRev[Total impressions]
)

Forum Discussion

Today()-1 vs Today()-2