PY YTD Measure that Ignores Month Slicer

[Anonymous]

Hi All,

I created a YTD measure that ignores my month slicer visual by using the following: 

CY YTD Actual $ =

CALCULATE([CY Actual $],ALL(Calender[Date].[Month]))

I would like to do the same for prior year. Below is my current measure for prior year, but it is still being filtered by my month slicer visual. Can you help me put the ALL( function into the below measure?

PY YTD Actual $ (No Filter) =

CALCULATE( [PY Actual $],

SAMEPERIODLASTYEAR(DATESBETWEEN(Calender[Date],STARTOFYEAR(Calender[Date]),

LASTNONBLANK(Calender[Date],[CY Actual $])

)

))

[amitchandak]

@Anonymous , for YTD  and last year YTD

Try

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year 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))

Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hi @amitchandak ,

When I use your Last YTD Sales as shown below & I select a month in my slicer it filters this PY YTD column to the selected month. What I am trying to do is show the CY YTD & PY YTD columns as true year to date columns that ignore my month slicer (I have my month slicer because I want other columns to filter based on the month selected).

PY YTD Actual $ (No Filter) =

CALCULATE(SUM('PY Inv Sales'[CM]),DATESYTD(DATEADD(Calender[Date],-1,YEAR),"12/31"))

[amitchandak]

@Anonymous , Can you share an example. I checked in a shared blog file, If I filter Aug 19, I get YTD till Aug 19 and prior YTD till Aug 18.

If want to ignore slicer, try this year vs last year option.

This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

[Anonymous]

This helped me solve my issue while browsing for solution. Thanks!!

🙂

[Anonymous]

Hi @amitchandak 

Here is the error i receive when I use the last year function 

Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

[amitchandak · Accepted solution]

@Anonymous , All(calendar(Date) is not closed. ) is missing . Remove one at the end