Forum Discussion
Helper IYTD Sales-Same Period Last Year
Hello All,
I have a requirement that I have been working on for the last week or so, basically YTD Sales/Cost with Same Period Last Year in a grid. The report owner does not like how it has been created, as I have found that I need a slider/filter in order to get the TOTALYTD AND SAMEPERIODLASTYEAR to work properly. They do not want the user to have to select the date, as shown below. Is there a way to get the DAX functions to work without a filter/slider?
12 Replies
- dedelman_clng
Community Champion
Can you expand a little more on the requirement for the final data / visual ? Also, if we can see a picture of the data model and some of your measures that you say aren't working, that would be helpful.
-David
- umpoohg
HI David,
Thank you for responding. The measures are working in my current configuration, however if I remove the slicers, the functions do not work as I would expect them to. The business just wants to open the report, either via the PBI Service and the report to know what "Today" is, and thus just show the YTD sales, and then the amount of sales last year at the same point in time. So, for example, I want to know what the sales this year, up to 03/23/2017 and then sales YTD for 03/23/2016
Here are the measures I am using:
YTD Sales = TOTALYTD(SUM('bi sales'[TotalLineSales]), DateTable[Date])
YTD Costs = TOTALYTD(SUM('bi sales'[TotalLineCost]), DateTable[Date])
SPLY Sales YTD = CALCULATE([YTD Sales], FILTER(ALL(DATETABLE),DateTable[Year]=MAX(DateTable[Year])-1),SAMEPERIODLASTYEAR(DateTable[Date]))
SPLY Costs YTD = CALCULATE([YTD Costs], FILTER(ALL(DateTable), DateTable[Year]=MAX(DateTable[Year])-1),SAMEPERIODLASTYEAR(DateTable[Date]))
Please let me know if I can provide any more information. Thank you, again.
- dedelman_clng
umpoohg First off, I think you have doubly filtered your LY measures. SAMEPERIODLASTYEAR is an implict filter.
SPLY Sales YTD = CALCULATE ( [YTD Sales], ****NOT NEEDED --> FILTER ( ALL ( DATETABLE ), DateTable[Year] = MAX ( DateTable[Year] ) - 1 ), <-- NOT NEEDED**** SAMEPERIODLASTYEAR ( DateTable[Date] ) )As to your requirements, I think that is going to depend on how the data is refreshed. I have a similar report (that I have to refresh manually because of the data source), and I had to put in some extra M code (in the Edit Query area) to get the date table to only populate thru the current day:
...
(a) #"Changed Type" = Table.TransformColumnTypes(#"Inserted Start of Month",{{"Date", type date}}), (b) #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= Date.From(DateTime.LocalNow())) in (c) #"Filtered Rows"**(a), (b) and (c) markers are there for reference only. Do not put them in the code.
You would need to put (b) in via the "Advanced Editor". Change "#Changed Type" in (b) to the value at the beginning of (a). Change (c) to have the same name as the beginning of (b).
All that being said, since I have to manually refresh my data to get it up-to-date, I don't know if in a DirectQuery scenario (where the data is live, which is what I am inferring from your use case) the dates will update automatically with the rest of the transactional data. You may have to build the date table in your data source. Hopefully this should get you moving in the right direction.
Hope this helps,
David
Dear friends, unfortunately this function has no solution if there is an error. It is the worst function of Power BI, because it is necessary but only works in an unknown condition and does not work in others. It's a pity that this happens in an excellent solution like Power BI. There will be no solution. If it works, use it if you do not use any other function. That's the truth.
