Need Filter on Sumx to Ignore slicer filters using dates

[lasmithfla]

I have this calculation.  What I'm trying to do is regardless of what the selection of dates in the slicer are from the calender table; I want the total of HC for the last 12 months from the LAST DATE of data that we have.

The calc below will start the calc with the last date in the slicer filter, not the last date of data.  For example, if my last date of HC was June 2020, I want it summed from June 2020 back through July 2019.   

I may have a chart that only shows 6 months of data based on dates in the slicer from calendar table - so for example if July 2019 through Jan 2020 is selected, I want to see that date in the chart.  BUT in a card, I still want the Sum of the HC to be from the Max date in date set which is June 2020.

I'm not sure of the format of where to include an ALL or ALLEXCEPT?  Would that do it?

TotalHC12Months = Sumx(DATESINPERIOD(HeadcountPSWDSummary[Data Date],LASTDATE(HeadcountPSWDSummary[Data Date]),-12,Month),[Employee Count])

 tried this also - I have a dates table (Calendar table) which is what the slicer is pulling the dates from.  I need the Max date of our HC data and to ignore the slicer with the calendar date range. Everything I come up with is calculating 12 months from the ending slicer date, not my max data date.

TotalHC4 = CALCULATE(Sum(HeadcountPSWDSummary[HeadCount]),DATESINPERIOD(HeadcountPSWDSummary[Data Date],Max(HeadcountPSWDSummary[Data Date]),-12,Month),ALLEXCEPT('Calendar','Calendar'[PKDate]))

[dax · Accepted solution]

Hi @lasmithfla , 

It seems that you want to get last 12 total value based on max date of table instead of slicer , right? I think you could set interactive on Card, which should work(chart will change based on slicer , but card won't). You could refer to my sample for detail.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@lasmithfla , with date table you can do that.

TotalHC12Months = calculate(Sum([Employee Count]),DATESINPERIOD(Date[Date],max(HeadcountPSWDSummary[Data Date]),-12,Month))

You need sum or count

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


Appreciate your Kudos.

[lasmithfla]

TotalHC12Months = calculate(Sum([Employee Count]),DATESINPERIOD(Date[Date],max(HeadcountPSWDSummary[Data Date]),-12,Month))

That Calc returns the 12 months starting with the max date in the date slicer, not my max HC date.  That's what I've run into with every version of this calc that I've done.

[lasmithfla]

I do have a date table, that is what the date filters in the slicer are based on.

So for the graph visual for example, its based on whatever dates are selected from the date table.

The "card" I want it to ignore the DATE slicer because I need latest HC data, minus 12 months.  My date table goes out a few years so using that Max date won't work.

But because both visuals are in same report, the card keeps defaulting to the dates from the calendar slicer, it's using the MAX date of whatever date is selected in the slicer vs the LAST date of HC that we have in the HC table.