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
Chris
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.
Cheers Sam
Does it make any difference if you use LASTDATE rather than MAXX.
Good Luck for the Summit
Also,
I am away from home when the summit is on, will you be posting the Videos and Sample Files.
Cheers
Chris
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
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
Now, when I turn the visual back into a table, it will look like this;
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
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.
Chris, here is the video on this
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
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)
Thanks Sam,
I’ll give it a go later on today and will let you know the result
cheers
Juno
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
Thanks a lot!!
Juno
That’s great, thanks
Hi Sam,
I just watched the video you made regarding this post and as always, very informative. However, it didn’t work for our data model because we have a unique calendar set up. We have a Calendar table that actually has a three rows for every date. One for the regular calendar date and then two for different Fiscal dates. We then use a bridge table called Date to filter the three dates down to one date.
This data model structure allows our customers to restate reports between calendar and fiscal with a slicer/filter which is cool. But because of this multi-calendar support, we can not use time intelligence functions in DAX and our Calendar Date has to remain a string data type which limits some of the features of Power BI. Furthermore, we have to add additional logic using nested Calculate statements to derive MTD, QTD, & YTD measures.
For Sales YTD the DAX looks like this:
Sales Amt - YTD = CALCULATE(
CALCULATE ([Sales Amt],
FILTER (ALL ( 'Calendar' ),
'Calendar'[YearNumber] = MAX ( 'Calendar'[YearNumber] )
&& 'Calendar'[Calendar Date] <= MAX ( 'Calendar'[Calendar Date] ))
), FILTER('Calendar', 'Calendar'[CalendarPatternSKey] = 'Calendar Filter'[SelectedCalendar]))
I also included a pbix with a slimmed down version of this model. Can you think of a way to get our Sales Amt - YTD to stop projecting out in the future without losing the multi calendar support?
PS. Sorry for the long question!
Sales Amt YTD.pbix (3.8 MB)
I think I answered my own question. This DAX appears to work!
Sales Amt - YTD 3 =
VAR LastSalesDate =
MAXX ( ALL ( Sales[Order Date] ), Sales[Order Date] )
VAR YTDSales =
CALCULATE (
CALCULATE (
[Sales Amt],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[YearNumber] = MAX ( 'Calendar'[YearNumber] )
&& 'Calendar'[Calendar Date] <= MAX ( 'Calendar'[Calendar Date] )
)
),
FILTER (
'Calendar',
'Calendar'[CalendarPatternSKey] = 'Calendar Filter'[SelectedCalendar]
)
)
RETURN
CALCULATE (
IF ( MIN ( 'Calendar'[Calendar Date] ) <= LastSalesDate, YTDSales, BLANK () ),
FILTER (
'Calendar',
'Calendar'[CalendarPatternSKey] = 'Calendar Filter'[SelectedCalendar]
)
)
Can you think of a better way to support multiple calendars AND be able to leverage Time Intelligence functions without having measure explosion?
Sales Amt YTD.pbix (3.8 MB)
This is interesting.
Thinking about this a little more, I’m not sure you need an original table with 3 dates.
You really want to be enabling the time intelligence functions. Your DAX measures can become a little more complex that they need to be in my view. I see you’ve realised this.
I’m looking through the model and will see if I can identify a way to clean this up a bit.
Just doing a good review here…
I’m just not sure you need the calendar table to be like to get what you need. To me it could all be in the date table.
For example in the date table that I demo a lot, you can add custom financial year (or any time frame filters) to this standard date table. The key is to add the correct logic to the side of the table. That is how you can get all your filters quickly and easily across any time frame.
You’ll see in this example I just add them to the side.
This would also reduce the size of your table quite a bit also.
Because all the details seem to be the same around the date, it’s just the dates time window you need to adjust from what I can tell. I believe this can be done with the one table, with the date column referenced once.
This would simplify things immensely for you I believe.
Let me know if I’m missing something.
Chrs