Forum Discussion

elatreille's avatar
elatreille
Icon for Helper I rankHelper I
9 years ago
Solved

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 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?

Need Help
  • v-chuncz-msft's avatar
    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]
)

4 Replies

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
      elatreille
      Icon for Helper I rankHelper I
      9 years ago
      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.
  • elatreille's avatar
    elatreille
    Icon for Helper I rankHelper 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's avatar
      v-chuncz-msft
      Icon for Community Support rankCommunity 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]
)