topic Getting Sales of Previous Period with Multiple filters in DAX Commands and Tips

Getting Sales of Previous Period with Multiple filters

Daplis — Fri, 23 Apr 2021 11:11:27 GMT

Hello! Can't seem to get the correct value for sales of previous period when multiple sliders are involved. Already tried multiple solutions but I can't figure out what's missing. All the data are placed in a single table already to simplify the visualization.

DeptSalesByType Table columns:

Sales (Fixed Decimal Number)

Department (Text)

Transaction_date (Whole Number)

Type (Text)

FullDate (converted Date/Time from Transaction_Date)

4 Sliders:

Year (2017 | 2018 | 2019 | 2020)

Month (Jan | Feb | Mar | .... Nov | Dec)

Type ( All | Loyalty | Non-Loyalty)

Department (Cosmetics | Kids | Mens | Womens)

Measures:

Sales Previous Period = CALCULATE(SUM(DeptSalesByType[ Sales ]),DATESBETWEEN(DeptSalesByType[FullDate],[Start Previous Period],[End Previous Period])) -- always Blank result

Sales Previous Year v2 = CALCULATE(SUM(DeptSalesByType[ Sales ]),

FILTER

(ALL(DeptSalesByType[FullDate]),DATESBETWEEN(DeptSalesByType[FullDate],[Start Previous Period],[End Previous Period])

)) -- same result as the sales of current Period

Sales Current Period = CALCULATE(SUM(DeptSalesByType[ Sales ]),DATESBETWEEN(DeptSalesByType[FullDate],[Start Current Period],[End Current Period])) -- Working

Re: Getting Sales of Previous Period with Multiple filters

Anonymous — Fri, 23 Apr 2021 14:52:56 GMT

Daplis

This is a question about DAX, not Power Query... Can you please post this on the right forum?

A very quick hint why this does not work can be found at

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

and

Time Intelligence in Power BI Desktop - SQLBI.

Re: Getting Sales of Previous Period with Multiple filters

Anonymous — Tue, 27 Apr 2021 06:23:46 GMT

Daplis

You only a Full Date column, I don't see how did you get the Start and End period, can you just share sample pbix.

Paul Zheng _ Community Support Team

Re: Getting Sales of Previous Period with Multiple filters

Daplis — Tue, 27 Apr 2021 10:32:01 GMT

Hi,

Here they are:

Start Current Period = FIRSTDATE(DeptSalesByType[FullDate])
End Current Period = LASTDATE(DeptSalesByType[FullDate])

Start Previous Period = [End Previous Period]-[Days Current Period]
End Previous Period = [Start Current Period]-1

Days Current Period = DATEDIFF([Start Current Period],[End Current Period],day)

Re: Getting Sales of Previous Period with Multiple filters

Daplis — Fri, 30 Apr 2021 09:04:00 GMT

Actually managed to solve this. Had to create a duplicate table of the FullDate and it worked. Not sure if it's the right way though as I made the date redudant.