cancel
Showing results for
Did you mean:
Helper III

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

Hello,

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.

Can you help me with that ?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper III

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

My Issue is Solved in another ticket.
https://community.powerbi.com/t5/Desktop/Not-getting-the-YTD-of-previous-year-using-measures/m-p/940...

Thank you all for the support.

5 REPLIES 5
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))
``````

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://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

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

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

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

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

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

Hi,

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
Helper III

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

My Issue is Solved in another ticket.
https://community.powerbi.com/t5/Desktop/Not-getting-the-YTD-of-previous-year-using-measures/m-p/940...

Thank you all for the support.

Announcements

#### 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!

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

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

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

Top Solution Authors
Top Kudoed Authors
Users online (443)