topic Re: Same period last year in DAX Commands and Tips

Same period last year

thebeginner1204 — Mon, 26 Sep 2022 13:35:16 GMT

Hello together

I am currently trying to apply YTD to last year.
Unfortunately, this works neither with "sameperiodlastyear" nor with a "Dateadd" filter.

I would like to display the current value (YTD) and the last year in an identical period in a bar chart.

The following formula works with a slicer:

= CALCULATE(SUM(TBL_Sales)]),SAMEPERIODLASTYEAR('Date Slicer Choice'[Date]))

But I want to solve this without a slicer on the dashboard.
does anyone have a tip for me? THX a lot

Regards,

Re: Same period last year

amitchandak — Tue, 27 Sep 2022 01:58:09 GMT

thebeginner1204 , First of all make sure 'Date Slicer Choice' is date table, marked as date table and joined with Only date of Fact Table

CALCULATE(SUM(TBL_Sales[Sales]),SAMEPERIODLASTYEAR('Date'[Date]))

CALCULATE(SUM(TBL_Sales[Sales]),dateadd('Date'[Date],-1, year))

for YTD , please find exmaples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Once you select a date it will not show trend for YTD. It will show YTD values for that date. refer this Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Re: Same period last year

thebeginner1204 — Tue, 27 Sep 2022 12:00:29 GMT

amitchandak Thank you for your explanation.

Q1:

CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

-> What is the meaning of "[net] in this example?

Q2:

PBI Desktop has an error "No single value can be determined for the column "Date" in the table "Date". This can happen when a measure formula refers to a column with many values without specifying an aggregation such as MIN, MAX, COUNT or SUM to get a single result."

i have try the following syntax:

CALCULATE(SUM(TBL_Sales[Betrag]),DATESBETWEEN('Date'[Date],_min,_max))

and your Proposal

CALCULATE([Reveneu],DATESBETWEEN('Date'[Date],_min,_max))

both with the same error. what is the reason for this?

thank you in advance, Regards