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

Filter and compare different time periods and graph results with dates on axis

Hi all, After following the following article https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/ trying to reproduce a table showing the difference between two values from two different time ranges , I wasn't able to plot the results in graph. For some reason when I add the the date column into a table or line visual, the date range covers all the dates even though it is filtered by my two slicers. I created a two date calendars as specified in the article. Both are connected through a 1:1 inactive relationship. And only my main date table connects to my data table. To get my previous_session value filtered using a second slicer i entered the following formula : previous sales = CALCULATE( [Sessions_measure], ALL('Date'), USERELATIONSHIP('Date'[Date],'Previous Date'[Date]) ) Having sessions & previous sales into the same table gives the correct values. However, when I drag the date of either table into the table visual, all the dates are shown (2000-2020) and the calculated previous sale filtered by my second slicer is duplicated in each row. My first slicer is 2018-11-01 to 2018-11-02 and my second is 2018-10-01 to 2018-10-02.
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter and compare different time periods and graph results with dates on axis

hi, @RogerSteinberg

If you want to add the date field in your graph/table.  Try this way as below:

Step1:

Add a new date table

you could just use this formula create a new table

new Date = 'Date' 

Step2:

Create a relationship as below:

15.JPG

Note: be care for the cross filter direction is singel

Then use date field from new table in your graph/table.

 

and here is new pbix file, please try it.

 

Best Regards,

Lin

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

Re: Filter and compare different time periods and graph results with dates on axis

hi, @RogerSteinberg

You should not add a date field for [Sales Amount] and [previous sales], because they are calculated based on different date table.

If you want to add a date field for them, you need to add the date field for two measure separately.

For [Sales Amount] you need to add date field from Date table

and for [previous sales] you need to add date field from Previous Date table.

You need a suitable normalization factor to normalize the values. There might be many, and this is a business decision more than a technical decision. In this example, we go for a simple normalization based on the number of days. If you divide sales by the number of days in the Previous Period selection, you obtain the average sales per day in the previous period. This value, multiplied by the number of days in the Current Period (the Date table) produces a normalized value for the previous selection:

Normalization Factor :=
DIVIDE (
    COUNTROWS ( 'Date' ),
    COUNTROWS ( 'Previous Date' )
)

Because of [Normalization Factor], the result of  [Previous Sales] will be normalized.

You may try to remove it and test again.

Previous Sales = CALCULATE(
	 [Sales Amount], 
	 ALL ( 'Date' ), 
	 USERELATIONSHIP( 'Date'[Date], 'Previous Date'[Date] ) 
) *[Normalization Factor]

 

and here is the pbix file based on the article, please try it.

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RogerSteinberg Regular Visitor
Regular Visitor

Re: Filter and compare different time periods and graph results with dates on axis

Hi @v-lili6-msft,

 

Thank you for your answer. How can I do what you suggested if I want to add the date field in my graph/table. The point  is to see the granular data over the days selected, not just the aggregated amount. Your file applied what the article proposed as "temporary solution" before PowerBi comes up with something more efficient. But the article's solution is still very limited.

 

Thank you.

Community Support Team
Community Support Team

Re: Filter and compare different time periods and graph results with dates on axis

hi, @RogerSteinberg

If you want to add the date field in your graph/table.  Try this way as below:

Step1:

Add a new date table

you could just use this formula create a new table

new Date = 'Date' 

Step2:

Create a relationship as below:

15.JPG

Note: be care for the cross filter direction is singel

Then use date field from new table in your graph/table.

 

and here is new pbix file, please try it.

 

Best Regards,

Lin

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RogerSteinberg Regular Visitor
Regular Visitor

Re: Filter and compare different time periods and graph results with dates on axis

Hi @v-lili6-msft

 

I always compare dates with the same total number of days

For example: July 3-5 2018 to Aug 13-15 2018.

I need the column of sales amount (based on current period) and previous sales (based on previous period ) to be side by side so i can evaluate the difference between them and the % difference.

 

And ultimately graph the results so we could see the lines overlapping. The dates on the x-axis would be the current period date.

Is that possible?


+------------+--------------+----------------+------------+
| Date           | sales amount | previous sales | difference |
+------------+--------------+----------------+------------+
| 2018-08-13 | 5000           | 3000                 | 2000 |
| 2018-08-15 | 7000           | 4000                 | 3000 |
+------------+--------------+----------------+------------+

 

 

Thank you for your help!