cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sach18 Helper III
Helper III

Calculate CYTD and PYTD depending on the filter slicer in power bi

Hello,

Please help me on the following 
I have many filters like Region, Territory, Product and Year. On checking year, I have to update the checked year YTD and checked Previous year YTD.

cap.JPG

 


Can you help me with that ?
 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
sach18 Helper III
Helper III

Re: Calculate CYTD and PYTD depending on the filter slicer in power bi

5 REPLIES 5
Super User IV
Super User IV

Re: Calculate CYTD and PYTD depending on the filter slicer in power bi

In case you have date table and the year is coming from that. You can have measure like these

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


sach18 Helper III
Helper III

Re: Calculate CYTD and PYTD depending on the filter slicer in power bi

Hey @amitchandak ,

I am not using any date table as I am connecting 3 tables and each table has region, Territory and Product to be filtered.
For Example, each date will have multiple products/Region/Territory. But in the DateTable, we can have only one date.

in my table
6th March 2019 - Central Region
6th March 2019 - North East Region

6th March 2019 - South Region

in date table, I can't have 3 rows with same date right ?

Let me know if I can do anything else.

Super User IV
Super User IV

Re: Calculate CYTD and PYTD depending on the filter slicer in power bi

Date table will have only date and date related columns like the year , month, qtr, etc. Why we need a region in that. that should be part of a different dimension.

 

Refer this file we have date and other common dim between few tables :https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Microsoft v-gizhi-msft
Microsoft

Re: Calculate CYTD and PYTD depending on the filter slicer in power bi

Hi,

 

What's your relationships and linked columns among your three tables?

I advise creating a dimension date table to filter these tables with one-to-many relationship.

Please create a date-slicer-table like this:

DateSlicerTable = DISTINCT(SELECTCOLUMNS('Table',"Date",'Table'[Date]))

 

Best Regards,

Giotto Zhi

  

Highlighted
sach18 Helper III
Helper III

Re: Calculate CYTD and PYTD depending on the filter slicer in power bi

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
Users online (443)