Time - intelligence: YTD/ LYTD

[auxilio99357]

Hii!

I´m a begginer with PowerBI and have some problems with the time-intelligence functions im using.

I have two tables, the first one is a sales table with the following columns: client/date/units (for 4 years) and a date dimension table. The only relationship between them is a many to one single relationship of the date.

I create his measures:

YTD = TOTALYTD(sum(Sales[Units]),DimDates[Date])

LYTD = CALCULATE(sum(Sales[Units]),SAMEPERIODLASTYEAR(DimDates[Date]))

MTH = CALCULATE(sum(Sales[Units]),DATESMTD(Sales[Date]))

LMTH = CALCULATE([MTH],PREVIOUSMONTH(DimDates[Date]))

MTH_PROM = CALCULATE(AVERAGE(sum(Sales[Units]), filter(all(DimDates[Date]),DATEDIFF(DimDates[Date],TODAY(),MONTH)>=1 && DATEDIFF(DimDates[Date],TODAY(),MONTH)<=12))

and this are the problems with the measures:

YTD: it works ok when i use a page slicer and select the year but i want to create a page where i compare YTD of the current year and the same period last year without filtering the year and both YTD and LYTD brings a blank value

MTH_PROM: I use this measure in a page that has a page level filter = 2020 and a page slicer that alow you to chose a month (january - december), the idea is that the measures brings the value of the average of the 12 months prior to the month you choose in the filter. The thing is that i dont know if its possible to connect the slicer to the measure for this to happen.

I hope you can help me

Thank you very much!!!!!

[v-janeyg-msft]

Hi, @auxilio99357 

According to your description, I think you must know that the value of measure will change with the context. It is under the action of each visual, slicer and filter. You use the time intelligence function, and the formula isn't rigorous, so the expected value doesn't appear in different visuals(The formulas you use are the most basic. Once the context is complicated, wrong values will appear.).

Can you share some fake sample data and your desired result? So we can help you soon.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[auxilio99357]

Hi @v-janeyg-msft 😊

Thank you for your help, I will try to be as clear as possible

I have two data tables, the first one is like this:

(note that all of the sales of a month are charged to the first day of it)

The other one is a common date dimension table.

The YTD and LYTD measures I want to display it on a simple table like this:

The things is that I want the LYTD measure to sum the months of the previous year till the month we currently are. In the example data the current month is april 2020, so the LYTD formula value should be ´0´ for every client, but is not.

And the other thing I cant solve is the issue with the PROM measure, I want to display it in a table like this (the photo is from the original data):

The idea is that you choose a month from the slicer and the PROM measure calculates the average of the previous 12 month not inlcuding the current month. 

Please let me know if something is not clear.

Thank you!

[v-janeyg-msft · Accepted solution]

Hi, @auxilio99357 

Yes, if you use time intelligence functions, such as TOTALYTD, then you need to select a specific date, such as 2020. If the context does not have a specific year,it will select the date with the largest date, so an error occurs.

In a line chart with a year, the time intelligence function should be no problem, but if it used in the table, you need to rewrite the measure and specify a specific year.

Like:

2020YTD =
CALCULATE ( SUM ( Sales[Units] ), YEAR ( DimDates[Date] ) = 2020 )

If you only put the month on the slicer, it will include all the corresponding months of the year, then there will be loopholes in the logic you said. Please try to understand what I said and make changes.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[auxilio99357]

Hi @v-janeyg-msft

I understand what you said and I rewrite the measure YTD and it works ok.

But now i don´t know how to change de LYTD measure, because if I do the same with LYTD  like this:

2020LYTD =
CALCULATE ( SUM ( Sales[Units] ), YEAR ( DimDates[Date] ) = 2019 )

The measure will sum up the whole 2019 instead of the parcial months. I can filter the months manually but my goal is to write a measure that calculate the results automatically.

Thats why I add the page filter (Year (from DimDates) = 2020) and expect that this measures work:

YTD = TOTALYTD( SUM ( Sales[Units] ), DimDates[Date] )
LYTD = CALCULATE( [YTD] , SAMEPERIODLASTYEAR ( DimDates[Date] ) )

but again LYTD sums up the whole 2019.

Can you think another why to solve it?

Regards!

Auxilio99357

[v-janeyg-msft]

Hi, @auxilio99357 

You want the data not to change according to the filter, right?

If yes, you can try:

2020LYTD =
CALCULATE ( SUM ( Sales[Units] ), YEAR ( DimDates[Date] ) = 2019 ,filter(all(table)))

Best Regards

Janey Guo

[littlemojopuppy]

@auxilio99357  without seeing a screen shot or something to indicate how you're using the YTD measure.  But time intelligence functions require some kind of reference to the date table in order to determine what period of dates they should use.  Whether that is a slicer, an axis on a chart or matrix, or even a filter on the visual/page/report.  But it has to have some way to figure out the appropriate time period.

The second question...if I'm understanding you correctly, you're trying to be able to figure out up to but not including the current month?  Maybe???  🙂  If that's the case try this measure instead.

CALCULATE(
	AVERAGE(sum(Sales[Units]),
	EXCEPT(
		DATESYTD(Calendar[Date]),
		DATESMTD(Calendar[Date])
	)
)

Hope this helps!  🙂

[auxilio99357]

Thank you @littlemojopuppy !!

I´m sorry the post wasn´t clear enough! 

I´m using the YTD and LYTD in a Table like this (the 'convenio' column represents the clients)

My sales table contains data from 2017 to 2020 but i want the YTD to only show the sum of 2020

If I add a Page level filter, filtering 2020, the measures work:

but I have on the same page an area chart like the following, that when i add the 2020' page level filter shows only a point:

Maybe there is a way to change the chart instead of the measures.

And about the second question, I use the measures also in tables like this:

The idea is that you choose a month from the slicer and the PROM measure calculates the average of the previous 12 month not inlcuding the current month as you said (in this case I also have date from 2017 - 2020, so I add a page level filter to the page filtering only 2020). Your formula worked! but it calculates the average of only the months in 2020 becasue of the filter. 

Thank you!!! 🙂