Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RogerSteinberg
Post Patron
Post Patron

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

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.

View solution in original post

9 REPLIES 9
v-lili6-msft
Community Support
Community Support

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.

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.

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.

Thanks a lot!!!

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!

@RogerSteinberg  did you ever get this one fully solved for your specific needs? I'm working on the same problem. 

Nope... I dropped it . 

@RogerSteinberg Not sure if you're still interested in this at all, but I seem to have cracked it! I've been working on it on and off for months. Like you, I kept dropping it. But I figured it out yesterday.

It's not a perfect solution, as you'll see, but it's very close. 


Okay, so you have your

Sales Table
Period Table
Previous Period Table

Keep all your current relationships, no need to add new ones. 

Step 1:
Create a line chart, and add your Date field from the Sale Tables to the Axis

Step 2:
Add Revenue/Sales as your first Value

Step 3: To get LY Sales:
1. Create a new measure, I called it "Date Diff" 

2. The goal here is to create a measure that gives us the diffence in days from your starting point from your current date selections between "Period Table" and "Previous Period Table"

3. Date Diff = DATEDIFF(MIN('Period Table'[Day]),MIN('Previous Period'[Previous Day]),DAY)
4. Create a second measure (You could do it in one, but I like having the "Date Diff" separated so I can use for other values if needed): LY Revenue. 

5. LY Revenue = CALCULATE(sum('Sales'[Revenue]),ALL('Period Table'),DATEADD('Sales'[OrderDate],[Date Diff],DAY))
        -We're summing revenue, but removing our Slicer filter from "Period Table", then we're looking back a number of days equal to the "Date Diff" formula we created in Step 3. 
6. Add LY Revenue as your second value

The caveat is that if you have a different number of days in your "Previous Period" you will still always show the same amount of days as the current "Period" - but I'm not sure when that might be a big issue. 

I also created another measure called "LY Day" and did the same exact calculation except that instead of 'Sales'[Revenue] I used 'Sales'[OrderDate]. I then added the normal 'Sales'[OrderDate] along with "LY Day" as tooltips. This allows my users to feel more assured when they hover over a certain point what two days they are comparing. 
If this isn't clear enough and you're interested, I can fill these formulas into that test case that was posted. 

@Hazenm I can't believe someone finally answered this question. That worked - with some adjusments within my objective - very well! Thank you! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.