Forum Discussion

Gregomex1's avatar
Gregomex1
New Member
3 years ago
Solved

Remove date filter context and apply another one

Hi PBICommunity 

 

I have a problem that i can't solve.

To illustrate the example, my model is composed of:

- a fact table : sales, dates, countries

- 2 dim tables : dates, countries

 

Dates from sales tables start at 1/1/2020

 

In the dataviz, I highlight sales amount and by country, using different timeframes (YTD, YoY, alltime...)  using DAX queries 

ex : SalesYTD = CALCULATE([sales] , DATESYTD( Dates[Date] ) )

So, alltime sales amount for every country are based on all dates, starting 1/1/2020

SalesAlltime = CALCULATE([sales] , ALL( Dates[Date] )

 

However, a single country needs to compute its sales from 1/1/2021, isolating existing sales prior to 1/1/2021.

The problem is when I calculate all time sales for a country , it take all dates row from date table.

 

How can I compute this "all function" for this specific country (all must return dates from 1/1/2021 to date only) ?

How could I use a date filter that would fit with all countries, including this specific country ?

 

Thank you !

 

Greg 

  • Hi,

    Try this measure pattern

    Measure1 = if(max(countries[Country])="India",calculate([sales],datesbetween(Date[date],date(2021,1,1),maxx(all(Date),Date[date]))),[salesalltime])

    Hope this helps.

2 Replies

  • Gregomex1 , You need to have a meausre like

     

    SalesAlltime = CALCULATE([sales] , Filter(ALL( Dates[Date] , Dates[Date] >= date(2021,01,01) ) ) 

  • Hi,

    Try this measure pattern

    Measure1 = if(max(countries[Country])="India",calculate([sales],datesbetween(Date[date],date(2021,1,1),maxx(all(Date),Date[date]))),[salesalltime])

    Hope this helps.