Today()-1 vs Today()-2

[elatreille]

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 to show on Power BI Service, it only shows yesterday but takes the place of the 2 days graph... its like Power BI doesn't like when we use Today() twice in the same graph.

Here are my 2 measures:

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

Two_Days = CALCULATE(SUM('AdvRev'[Total impressions]),'Calendar'[Date]=TODAY()-2)

So what did I do wrong?

[elatreille]

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 · Accepted solution]

@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]
)

[GilbertQ]

Hi there, 

What about if you change your measure from TODAY() to use DATEADD

Here is an example: 

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

[elatreille]

Both ways works for yesterday but not working for 2 daus ago so if I replace -1 by -2 for 2 days before, it returns the same numbers from yesterday.