Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Solved! Go to 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:
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
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
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:
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
Thanks a lot!!!
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! 🙂
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |