Sales YTD and PY calculation issue

DAX DAX Calculations
1

Hello,

I have been stuggling all day to get a sales YTD & PY calcuation to work as expected. Currently I have the following DAX calcutions:

1. Total Sales YTD =
VAR LastSalesDate = MAXX( ALL('Sales Transactions'[InvoiceDate]), 'Sales Transactions'[InvoiceDate])
VAR YTDSales = TOTALYTD([Total Sales], 'Date table'[Date])
RETURN
IF( MIN( 'Date table'[Date] ) <= LastSalesDate, YTDSales, BLANK())

2. Total Sales YTD PY = 
IF(LASTDATE('Date table'[Date]) <= TODAY(), CALCULATE([Total Sales YTD], SAMEPERIODLASTYEAR('Date table'[Date])),BLANK())

In the below image you will see that they are mostly working but the YTD PY is giving a blank in the totals. Where am I going wrong?

Blank%20PYTD
Blank PYTD.JPG1435×807 132 KB

2

It the TOTALYTD function which is causing the issues here.

It has quite unexpected behaviour depending on what date context you have in your report.

I can’t quite see what you have in your report because I don’t see a slicer.

But for example, if for instance your date table has dates in a particular year but then there is no data and your filter extend to that time frame that you total will be zero.

As in my example below.

image
image.png942×520 27.4 KB

If I reduce this time range to where there is data, then I get a total as expected.

image
image.png958×483 26.6 KB

See how you go reviewing things around this.

If you can upload an example file that would be helpful.

Thanks
Sam

3

Hi,

Thank you for the reply.

Unfortunately I cannot upload an example file as I am using live company data. This is isn’t helpful in this situation but I have decided to do this as I think this will give me good “real world” practice while I learn Power BI.

I didn’t have any slicer on this page but when I added it and selected the max date on the slicer to today, it worked so you are correct in where the problem is originating from. As a test, I also removed all other tabs from the report to ensure there was no conflict elsewhere but this made no change. The dates table I am using is the one you suggested in the training and it runs 2018 & 2019 full calendar years.

Since I don’t want to have a slicer on the page for sales YTD & PY YTD, want these to update automatically, I have tried a few tweaks to the formulas with no success.

Am I using the best formulas for what I am trying to achieve? I think the YTD formula is the best as this comes from your video on preventing YTD projecting forward, but maybe the PY YTD formula is indirectly treating the TOTALYTD function incorrectly?

PY%20YTD
PY YTD.JPG1920×1018 174 KB

Thanks
Ken

4

Any reason why you can’t just use the cumulative total formula pattern?

This is the simplest and most dynamic cumulative total calculation.

The YTD total can have some small issues like this depending on what context you have in your report at any time.

Thanks
Sam