CODEWITHSUNDEEP
daxcalculation
user3655159
user3655159

Reputation: 1

PowerBi - Sales for SamePeriodLastYear is incorrect

I have a PBI page that filters by period – i.e. month period P01, P02... P12 and year.

So when I sum the sales for this year, the max possible date is today or the date of the last refresh – in my case it’s currently 08.10.2023

But when I sum the sales using the ‘sameperiodlastyear’ filter, it also filters by the period and year for last year.

The problem is that when I filter by the period and year of last year it sums the full month of last year, and not up to same date in this year ie 08.10.2023

When I create a new chart showing the date by day formula, it works perfectly, but it reports incorrectly when only filtering by month.

This is the formula :

TGS PY YTD Simple = CALCULATE( CALCULATE( SUM('fact_Sales'[TGS (LC)]), DATESYTD('Date'[Date]) ), SAMEPERIODLASTYEAR('Date'[Date]) )

How can I get it to sum only to the refresh date for last year, and not the whole month?

I tried creating a calculatedtable, and summing that I also tried all forms of date formula's and so far nothing has worked

Upvotes: 0

Views: 416

Answers (1)

Smitteeh
Smitteeh

Reputation: 126

The SAMEPERIODLASTYEAR() will return the entire selected month because you filter only by period. If you want to filter the last year period until the last sales date, you will need to add that in the measure like this:

Sales Amount LY = CALCULATE([Sales Amount], SAMEPERIODLASTYEAR('Date'[Date]), 'Date'[Date] <= MAX(Sales[OrderDate]))

This will only show the last year amount till the last orderdate.

Upvotes: 0

Related Questions