Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello folks,
I have done some Google-Fu but was unable to come up with an answer or even someone trying to do the same thing.
As of now I have the current table:
Previous period comes from a separate date table, linked with my main date table through a 1:1 relationship. Now, on to what I am trying to do. The plan here is to set two reference dates, one last year, another one this year through different slicers, and have the table showing as this:
Ref | Previous Period | Target Period |
D-4 | 1.008.739,91 | 1.010.373,16 |
D-3 | 677.959,82 | 746.579,72 |
D-2 | 962.194,37 | 983.525.86 |
D-1 | 1.151.780,32 | 1.100.064,23 |
Total | (total above) | (total above) |
At first I thought of creating a new table with all D-1...D-30, and columns on the main date table and comparison date table which adjust the date according to the value set on the slicers, but I am not sure if this is possible. Any thoughts or ideas are highly appreciated.
Hi @7700-2 ,
Please create a new table like below and refer to the measure below.
Measure = CALCULATE(SUM('Table (2)'[value]),FILTER('Table (2)','Table (2)'[date]>=MIN('date'[date])-SELECTEDVALUE('Table'[Column2])&&'Table (2)'[date]<=MAX('date'[date])-SELECTEDVALUE('Table'[Column2])))
Pbix as attached.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, I think I understand your requirements. My suggestion is to create a new, not connected table like this:
Assuming that you have measures showing the value at the reference dates ([ValueRef1], [ValueRef2]), create new measures like this:
Previous Period = SUMX(Ref; CALCULATE([ValueRef1]; DATEADD(DatesPerviousPeriod[Date]; 'Ref'[Delta]; DAY)))
Current Period = SUMX(Ref; CALCULATE([ValueRef2]; DATEADD(DatesCurrentPeriod[Date]; 'Ref'[Delta]; DAY)))
So, if you combine the new table and those new measures I think you will get what you want.
For example, when showing the row with Ref D-1 Previous Period will show [ValueRef1] but with selected date moved -1 day.
Edit: Saw your updated requirements. How should you select the number of days to compare? I suppose you need to add some kind of filter to only display the selected number of days. For example the measures above could return BLANK() for rows after the number of days selected.
Hi @Anonymous, this is quite close to what I need. Dates would be selected through a single selection slicer, pre-filtered to only show past dates. I am using your inputs and trying to make it work.
From what I see here I would need some kind of column that changes values according to the selected value on the slicer, but dynamically changing columns is not something we can get on Power BI.
Worst case scenario, I'll have to create a separate Excel sheet with the reference dates and run all the calculations via PowerQuery 😞
Tough to say exactly without source data and such. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
But, if it were me, I would probably have disconnected tables for my slicers and grab their values in the measure and then perform the calculation on what to display. Hard to be more specific with the information provided.
Hi @Greg_Deckler and thanks for offering help. To make things simple, I have the following tables:
Dates
Date |
01/02/2020 |
02/02/2020 |
03/02/2020 |
04/02/2020 |
05/02/2020 |
06/02/2020 |
07/02/2020 |
Comparison dates (linked with Dates on a 1:1 relationship):
Date |
05/02/2019 |
06/02/2019 |
07/02/2019 |
08/02/2019 |
09/02/2019 |
10/02/2019 |
11/02/2019 |
Sales (linked to Dates on a 1:1 relationship)
Date | Revenue |
(every date for 2019 and 2020) | Revenue broken down by day |
So I have basically the same table structure as this article: https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/ - this was done so the report users could compare between different sets of dates.
Now the issue is, I need to get whatever dates are chosen to line up in a matrix or even a graph. So if I want to compare sales on the 10 days leading up to Thanksgiving for 2019 and 2018, or the 30 days prior to Easter on two different years, I will be able to by selecting the two dates in different slicers and have my revenue displayed in a D-1, D-2, D-3, etc.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |