Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, Please I need help to fix this.
I used the following formulas to create a measure for YTD and YTD LY
ll YTD = CALCULATE(SUM('Transaktion'[konto]),FILTER('Transaktion',[Datum]>=DATE(YEAR(TODAY()),1,1) && [Datum] <=(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1)))
ll YTD LY= CALCULATE(SUM('Transaktion'[konto]),FILTER('Transaktion',[Datum]>=DATE(YEAR(TODAY())-1,1,1) && [Datum] <=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)-1)))
when I choose the year 2018 in the year slicer I also want to see YTD LY(YTD value for 2017), but it only shows me YTD(for 2018)
Solved! Go to Solution.
I created a separate date table and I created a column
Today = DATE(YEAR('Table'[Date]),MONTH(TODAY()),1)-1
and to find a YTD I used the formula
ll YTD = TOTALYTD(SUM('Transaktion'[konto]),'Table'[Date],FILTER(ALL('Table'[Date]),'Table'[Date] <= MAX('Table'[Today])))
and to find YTD LY
ll YTD LY = VAR DataMaxDate =CALCULATE ( MAX ( 'Transaktion'[Datum] ), ALL ( 'Transaktion' ) )
RETURN
CALCULATE ([ll YTD],SAMEPERIODLASTYEAR (INTERSECT (VALUES ( 'Table'[Date] ),DATESBETWEEN ( 'Table'[Date], BLANK (), DataMaxDate ))))and it works fine!
Thank you for your replies!
Hi @Anonymous,
How did you use these formulas? In other words, what's the context? Can you share a sample file?
Best Regards,
Dale
I created this measures to get YTD values until the end of last month and LY YTD for the same period( last year until the end of last month e.g September)
Below is my sample file
Hi @Anonymous,
Your sample is a result which we can't use it to find the root cause. Can you share a dummy sample?
BTW, it seems you selected the year 2017 and there isn't the year 2016. Maybe that's why the YTD LY is empty.
Best Regards,
Dale
Hi @Anonymous,
There isn't any data in the year 2017. For example, IKB 2020. there isn't any data of IKB 2020 in the year 2017. What's the expected value?
Please also check out the demo in the attachment. If it's the desired result.
Best Regards,
Dale
I edited the formula by adding ALL function, and now it shows me YTD LY when I select 2018 I, but for each row, it shows the same value(total of YTD LY), instead of showing the actual YTD LY values for each IKB and put the total below.
What I want to see is, when I select each year I want to see the year to date value for the selected year and the year before.IKB like an organization number.
ll YTD LY = CALCULATE(SUM('Transaktion'[konto]),FILTER(all('Transaktion'),[Datum]>=DATE(YEAR(TODAY())-1,1,1) && [Datum] <=date(YEAR(TODAY()),MONTH(TODAY()),1)-1))
I created a separate date table and I created a column
Today = DATE(YEAR('Table'[Date]),MONTH(TODAY()),1)-1
and to find a YTD I used the formula
ll YTD = TOTALYTD(SUM('Transaktion'[konto]),'Table'[Date],FILTER(ALL('Table'[Date]),'Table'[Date] <= MAX('Table'[Today])))
and to find YTD LY
ll YTD LY = VAR DataMaxDate =CALCULATE ( MAX ( 'Transaktion'[Datum] ), ALL ( 'Transaktion' ) )
RETURN
CALCULATE ([ll YTD],SAMEPERIODLASTYEAR (INTERSECT (VALUES ( 'Table'[Date] ),DATESBETWEEN ( 'Table'[Date], BLANK (), DataMaxDate ))))and it works fine!
Thank you for your replies!
You will have to use an ALL or ALLSELECTED in your YTD LY calculation. 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-TIT...