Clear the filter and add a new one in DAX

[hpandersen]

Hello.

I'm taking stock of the number of different accounts for a given period and for the year to date.

I'm using a table with all transactions (ACTR) and a tabel with dates (Datotabel) since our exercise is not following a calender year.

The relationship between ACTR and DatoTabel is DatoTabel[Periode]->ACTR[ACPR].

I have a page filter for the period, so filtering for a given period is no problem.

The problem is as follows:

We're in period 6 right now. If I want to see the result for period 5, I can't figure out how to get my year to date to show from period 1 to 5, and not from period 1 to 6.

I tried the following formula, but it's only showing 5 - not YTD.

Actual_YTD calculate(SUM(AcTr[Amount]);all(Datotabel[Periode]);filter(AcTr; AcTr[AcPr]-SELECTEDVALUE(Datotabel[Periode]))

How do I delete the filter and apply a new one?

Any suggestions?

[amitchandak · Accepted solution]

@hpandersen , You can try with Time Intelligence and date like

YTD QTY forced=
var _max = eomonth(date(year(today()),month(today()-1),day(today())),0)
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

In case you financial year does not end ar 12/31, dates ytd can take year-end parameter

example

calculate(Sum('order'[Qty]),DATESYTD('Date'[Date],"3/31"),'Date'[Date]<=_max)

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/

[amitchandak]

In case you have a period not date use rank for that. Create w week Rank on Your period

Last 6 period Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[period Rank]>=min('Date'[Week Rank])-6 && 'Date'[period Rank]<=max('Date'[Week Rank])-1))

Refer to my Webinar: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...