cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rfernandes Regular Visitor
Regular Visitor

Remove Date filters within a date range

Hello everyone, 

I trying to ignore year and month filters, but inside a range of time. 

I working with a dabase that have a few years, the chart below shows the distinct count of every order ID. 

All years.png

For this measure i only want to work with year 2015 and 2016. 

Selected Years.png

My goal is to show  the table below, ignoring year and month filters. For example: Asia ll show 1006 in every month column(total of 2015 and 2016) and not 3687 (all years)

Result Table.png

 

With that, i want to ignore regions that have few orders ID based on year 2015 and 2016.

I tried a few things, but i can´t make it work.

 

Total sales = 
VAR id_qnty = CALCULATE(DISTINCTCOUNT(db[Order ID]); ALL(db[Date]); db[Date].[YEAR] in {2015;2016})
return
IF(id_qnty>1000; SUM(db[Total Revenue]);BLANK())

Here is the example file: example.pbix

 

Thank you!

3 REPLIES 3
Nick_M New Contributor
New Contributor

Re: Remove Date filters within a date range

What if you try the following:

 

1. Create a Calendar Table with the following code:

DimCalendar = 
ADDCOLUMNS(
    CALENDAR( 
        DATE( YEAR(MIN( db[Date])),1,1),
        DATE( YEAR( MAX ( db[Date])),12,31)),
        "Year", YEAR([Date]),
        "Month", MONTH( [Date] ),
        "MonthName", FORMAT( [Date], "mmmm")
)

Relate that table to your db table (1:M) on the date column from each

 

Use Year and Month for that new table as columns and rows on a new visual, like a matrix. Put Region on rows from the db table. Dont want to use the date columns from the db table, we want to filter the Calendar table and let that flow down to the db table.  

 

Add a Year slicer from the DimCalender table. Select a range from 2015 to 2016.  

 

Now we can just add this measure:

Measure = 
SUMX( Values( db[Region]),
IF(
    CALCULATE( [linhas], ALLSELECTED(DimCalendar))> 1000, 
    CALCULATE( [linhas], ALLSELECTED(DimCalendar))
)
)

and the final table

Final Table.png

rfernandes Regular Visitor
Regular Visitor

Re: Remove Date filters within a date range

Thank you for you answer @Nick_M !

But is there a way without using a calendar table? My model only base with one date column.

 

With the calendar table i used:

Measure 2 = 
VAR xxx = CALCULATE( [linhas]; ALLSELECTED(DimCalendar))

return IF(xxx>1000;Sum(db[Total Revenue]))

I didn´t understand the part 

SUMX( Values( db[Region])

 Thank you

Nick_M New Contributor
New Contributor

Re: Remove Date filters within a date range

Probably could work without date calendar, but it's generally not a good idea to use columns from your Fact table. You really want to use smaller Dimension table ( like the calendar table) to filter the fact table. 

 

The reason for the SUMX (Values..) in the front of that measure is for the grand totals. Without that I believe it would be the grand total for all the regions. But with SUMX ( Values( Region)) in the grand total it will just be the Regions that are visible in the current filter context, so just those three regions will be used to sum.