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

)

))

[Khalidtms]

Hi I have a accounting period date column from which I have month as a slicer
Need to show Current year YTD and whole Previous Year YTD both in a single table
Current Year YTD should be filtered by month and Previous Year YTD should ignore the month slicer selection and it should show Previous year YTD for the whole year.
For example:

Month	Current year YTD	Previous Year YTD
Jan	10	5
Feb	10	5
Mar	10	5
Apr	10	5
May	10	5
Jun	10	5
Jul	10	5
Aug	10	5
Sep	10	5
Oct	10	5
Nov	10	5
Dec	10	5

Results expected:
Month - July 
Current year YTD - 70 (From Jan to July)
Previous year YTD - 60 From (Jan to December)


Dax used : 

Previous year YTD =

 CALCULATE(

        [Current Year YTD],ALL('paqa FinancialAccount'[AccountingPeriod].[Date]),

        DATEADD('paqa FinancialAccount'[AccountingPeriod].[Date], -1, YEAR))

Current Year YTD = TOTALYTD(SUM('paqa FinancialAccount'[ValueUS]),'paqa FinancialAccount'[AccountingPeriod].[Date])

Please help me with revised Dax

[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