Ho

[Anonymous]

Hi

I have a table with 2 fields, Day, Sales.

Two questions

1-How can I calculate Sales for different time periods like Yesterday, Last Week, WTD, MTD, and YTD?

2-How can I compare each of those, with its equivalent in the previous year?

Thanks

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Firstly, suggest you to create a new date table, like below.

Date = CALENDARAUTO()

Reference:

Creating Calendar Table in Power BI using DAX Functions

• Yesterday

Yesterday = CALCULATE([Total Sales],PREVIOUSDAY('Date'[Date]))

• Last Week

Last Week =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Date' ),
        WEEKNUM ( 'Date'[Date], 2 )
            = WEEKNUM ( TODAY (), 2 ) - 1
    )
)

These are just samples. We can implement with different ways. You could reference the blogs to have a try.

Creating a dax calculated column for today,yesterday and next working day with variables 

Calculate Previous Weeks Sales - Advanced DAX in Power BI 

Week to Date Calculation in Power BI with DAX 

Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

Power BI does all of this. You need a Date table (see this for creating one that is dynamic), and a good understanding of Time Intelligence functions. You can start here to see if it enough. But there are some really good books on DAX that have chapters on Time Intelligence.

[MFelix]

Hi @Anonymous ,

First of all create a calendar table if you use the same table for calculate time inteligence measures you will get incorrect values due to filtering use.

Regarding the calculations you need you would need to have something similar to the measures below:

YTD = TOTALYTD (SUM(Table[Sales]); Calendar[Date])
MTD = TOTALMTD (SUM(Table[Sales]); Calendar[Date])
YESTERDAY = CALCULATE (SUM(Table[Sales]);FILTER(ALL(Calendar); Calendar[Date] = Today() - 1)

Comparision with previous YEAR would be something similar to:
YTD PY = CALCULATE([YTD]; DATEADD(Calendar[Date]; -1 ; Year)

These are just examples you may need to adjust them to your model or use some other calculations like the DATESBETWEEN or SAMEPERIOSLASTYEAR