Forum Discussion
LY YTD Calculation
Hi,
I need assistance with creating a DAX formula for LY YTD values.
FYI - I have a period slicer in my model (2 slicers, YEAR and QUARTER).
The problem is, the values in my model have an ABFlag that filters actual & budget values.
I want to calculate LY YTD, but because there are "budget" values that go throughout the whole year, when 2020 is selected; LY YTD calculates the entire last year, even with SAMEPERIODLASTYEAR being used.
How do I incoporate this into my DAX so that the SAMEPERIODLASTYEAR only counts the period for which there are "actual" sales?
Revenue includes both A & B, so I have created a measure.
This is the function that is returning the entire last year, but I need it to only pickup where there are actual sales.
6 Replies
- amitchandak
Super User
Please find the attached file. I have done it for qtr, but you can use the datesytd for ytd.
https://www.dropbox.com/s/9rkj9v2y74mdxny/Missingsales.pbix?dl=0
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/hi amitchandak & Greg_Deckler ,
Thank you both for your prompt replies.
Both solutions can calculate YTD, however cannot calculate YTD filtering "actuals" only.
I can workout YTD, but the key component is for the period criteria to be dependent on figures being present in "actuals" beyond a certain date.
Cheers,
Dan
- Greg_Deckler
Community Champion
Anonymous - Going to need to get some sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
- Greg_Deckler
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008 - amitchandak
Anonymous ,
with date calendar one of these should work
Year behind Sales = CALCULATE([RevA ],dateadd('Date'[Date],-1,Year))
Last YTD Sales = CALCULATE([RevA],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
