Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date Range (User Input) Conditional Filtering dashboard result

Hi All When I look at a sample dashboard  https://app.powerbi.com/view?r=eyJrIjoiMGQ0OTE0OTMtMGI3Ny00YmMxLWIwNjItNzA5NGU2NGQzNjU3IiwidCI6IjU3NGMzZTU2LTQ5MjQtNDAwNC1hZDFhLWQ4NDI3ZTdkYjI0MSIsImMiOjZ9...
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    Typically a common date calendar dimension is created and joins with all fact dates. Now all fact will get filtered with date of slicer from date dimension. Date dimension can month-year, year, qtr-year , week etc . All all that will also filter fact based on selection. But time intelligence can change behavior. The end date to any time  intelligence matches with end date if slicer of selected value like a month .

    example

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))

     

    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/

v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, Anonymous 

 

Generally,  your data model will contain a calendar table. It is usually better to aggregate data
by year and month using columns of a calendar table (containing one row for each day) instead
of extracting the date parts from a single column of type date or datetime in calculated columns.
There are a few reasons for this choice. You obtain a model wherein all date attributes are included
in a separate table making it easier to browse data using a generic client, and you can use special
DAX functions that perform time intelligence calculations. Moreover, most of the time intelligence
functions require a separate Date table to work correctly.

 

Defining a separate Date table is a common practice in any star schema. Whenever you
have a date column you want to analyze, you should create a relationship with a Date table. If you
have multiple date columns in a table, you can create multiple inactive relationships to the Date table
in addition to a single active one.

 

You may create a calendar table with Calendar or CalendarAuto function. For further information, please refer to the following links.

https://docs.microsoft.com/en-us/dax/calendar-function-dax

https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

 

 

Best Regards

Allan

 

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