Current YTD Costs Displayed as a Static Card

[anelliaf]

Hello,

I'm trying to calculate the current YTD costs.  The current formula I have is:

PY - Sales = CALCULATE([SumOfCosts], 'Usage Details - 2'[Year])

The problem is it's displaying past years.

Is there a way I can use a filter to display only this year's total?

Thanks,

Frank

[v-shex-msft]

Hi @anelliaf,

You can add if statement filter to your formal to check the year and replace other year records to blank, power bi will auto-hide these blank records.

Regards,

Xiaoxin Sheng

[bfernandez]

This can be accomplished using the Visual Level filter.

You can use the date correlated to the [Usage Details - 2] column.

Select the card you would like to be filtered and do the following in the Filters pane:

[anelliaf]

I tried this approach before, but when I select a previous year, i.e. 2019, it changes the card value to 'Blank'?

[amitchandak]

Make sure you have a date table and try fomulas 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://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

[anelliaf]

I added the Date dimension table.  The column of 'Date' is of Date/Time data type.  However, when I go to create a 'Year' column, the data type is Whole Number.  If I change it to Date, the values in the column change to 1905.

[bfernandez]

Can you provide some screen shots of what you're seeing versus what you're expecting to see?

[anelliaf · Accepted solution]

I think I got it.  I chose the card and the year filter on the page, and then selected 'None' for interactions.  It looks like it is holding up.  I have a few more filters to test to see if that's the case.  Thanks for everyone's help so far.