YTD Showing for Months after Last Sale


#1

Hi All,

I have a simple measure =TOTALYTD([Total Sales], ‘Date’[Date]) which works ok but the YTD Total is showing for Months after the last Sale. I would guess I am just missing a FILTER that stops the Calculation at the Last Sale but struggling with it. YTD should not show beyond January.

Thanks

ChrisYTD


#2

Hi Chris, I believe it will always do this if your date table runs longer than the actual sales data.

What you would need to do is use IF logic to return blanks if the date is greater than the last sales date.


#3

Cheers Sam

Does it make any difference if you use LASTDATE rather than MAXX.

Good Luck for the Summit


#4

Also,

I am away from home when the summit is on, will you be posting the Videos and Sample Files.

Cheers

Chris


#5

You have to remove the date context somehow to work out the last purchase date. So LASTDATE by itself won’t work. Placing the ALL( Purchase Date ) inside the MAXX allows you to do that.

Yes will be posting video and resources


#6

Hi all,
I have a follow up question on the above.

I have implemented the formulas to cut off TOTALMTD Last year. Workes fine in my visual
image

Now, when I turn the visual back into a table, it will look like this;
image

Now, I actually need to calculate with the latest value this year vs last year.
In the example €909660 vs €982578
Looking at the totals, the lastdate filter on the list has no influence on this total.

It has probably to do with the granularity of the report (filter by year and/or month)

How can I manage beeing able to calculate with the max value last year when the granularity of the report is not date but month

thanks
Juno


#7

Something doesn’t look right to me in that calculation.

Can you include the actually formula you are using currently.

If you work through this example which solves this, you’ll see that the totals are correct.


#8

Chris, here is the video on this


#9

Hi Sam,
thanks for your reply.

It concerns a fact table with purchased items from our buying team.
I also have a seperate date table, (datum[date] with a relation to the purchase date from the fact table (Aankopen[Inkoopdatum])

I first made a measure for total purchases like this ;

*Inkoopbedrag = SUMX(Aankopen, Aankopen[Aantal gekocht]Aankopen[Originele prijs])

Then I made the measure for the MontToDate like this ;

**Tot Inkoop MTD = **
VAR LastInkoopDatum = MAXX(ALL(Aankopen[Inkoopdatum]),Aankopen[Inkoopdatum])
VAR MTDAankopen = TOTALMTD([Inkoopbedrag], Datum[Date])

**RETURN **
IF(MIN(Datum[Date]) <= LastInkoopDatum, MTDAankopen, BLANK())

To caclulate the Month to date Last Year I used the following ;

Tot Inkoop MTD LY = CALCULATE([Tot Inkoop MTD], DATEADD(Datum[Date], -1,YEAR))

To have the data stop at the last known purchase date I used the following measure ;
**Tot Inkoop MTD lastDate LY = **
VAR LastInkoopDatum = MAXX(ALL(Aankopen[Inkoopdatum]),Aankopen[Inkoopdatum])
VAR MTDAankopenLY = TOTALMTD([Tot Inkoop MTD LY], Datum[Date])

**RETURN **
IF(MIN(Datum[Date]) <= LastInkoopDatum, MTDAankopenLY, BLANK())

Looking at the data in table viewit seems to do exactly what I need, except for the total.
This remains the total amount for the enitre month, rather than the amount I am looking for

Worth mentioning is that my Page filter consist of a selection in Year/Quarter/Month.
Granularity of the table is by date, but I still need to show and calculate with the max end date amount last year.

Thanks
Juno


#10

Ok try these formulas to get what you need.

Sales YTD = 
VAR LastSalesDate = MAXX( ALL( Sales[Purchase Date] ), Sales[Purchase Date] )
VAR YTDSales = TOTALYTD( [Total Sales], Dates[Date] )

RETURN
IF( MIN( Dates[Date] ) <= LastSalesDate, YTDSales, BLANK() )



Sales YTD LY demo = 
VAR LastSalesDate = MAXX( ALL( Sales[Purchase Date] ), Sales[Purchase Date] )

RETURN
CALCULATE( [Sales YTD],
    SAMEPERIODLASTYEAR(
        INTERSECT( VALUES( Dates[Date] ), DATESBETWEEN( Dates[Date], BLANK(), LastSalesDate ) ) ) )

This technique should solve it.

(It should be easier that this I know, but for some reason they make the totals a little hard to work out sometimes)


#11

Thanks Sam,
I’ll give it a go later on today and will let you know the result
cheers
Juno


#12

Hi Sam,
a bit late in reply, but the formula provided seemed to do the trick.
I can now calculate the difference in purchases for an incomplete month

image

Thanks a lot!!
Juno


#13

That’s great, thanks