Clear filter and add a new one in DAX

[hpandersen]

Hi.

I' making a balancesheet showing figures for different accounts for a given periode and for year-to-date.

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

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

I'm having a page filter for the periode, so filtering for a given periode is no problem.

The problem is the following:

Right now we are in periode 6. If I want to see result for periode 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 have tried with the following formula, but it is only showing periode 5 - not YTD.

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

How can I clear filter and apply a new one?

Any suggestions?

[amitchandak · Accepted solution]

If you have date and date table

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

YTD QTY forced=
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

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

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

[hpandersen]

Well.... that was easy. Did the trick, thanks 😉