Forum Discussion
Date Range (User Input) Conditional Filtering dashboard result
Hi All
When I look at a sample dashboard
I am interested in how it uses start and end date slicer to control the visual like KPI and graphs.
My questions are
1) What DAX query need to be involved in this particular pbix to obtain Gross Sales for example.?
2) Is it because a relationship had been established between the calender table and other tables like Sales table etc, hence when the user amend the date, the visual gets updated?
I
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/
2 Replies
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/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.
