cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Samrawit21 Helper IV
Helper IV

YTD and YTD prior with filter

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)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Samrawit21 Helper IV
Helper IV

Re: YTD and YTD prior with filter

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!

 

View solution in original post

8 REPLIES 8
Super User IV
Super User IV

Re: YTD and YTD prior with filter

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...


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Microsoft v-jiascu-msft
Microsoft

Re: YTD and YTD prior with filter

Hi @Samrawit21,

 

How did you use these formulas? In other words, what's the context? Can you share a sample file?

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Samrawit21 Helper IV
Helper IV

Re: YTD and YTD prior with filter

@v-jiascu-msft

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

InkedCapture_LI.jpg

Microsoft v-jiascu-msft
Microsoft

Re: YTD and YTD prior with filter

Hi @Samrawit21,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Samrawit21 Helper IV
Helper IV

Re: YTD and YTD prior with filter

@v-jiascu-msft Thanks for your reply!

Below is my sample file 

Capture2.PNG

 

Capture 3.PNG

Microsoft v-jiascu-msft
Microsoft

Re: YTD and YTD prior with filter

Hi @Samrawit21,

 

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.

YTD-and-YTD-prior-with-filter

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Samrawit21 Helper IV
Helper IV

Re: YTD and YTD prior with filter

Hi @v-jiascu-msft

 

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))

 

 

Samrawit21 Helper IV
Helper IV

Re: YTD and YTD prior with filter

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!

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors