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
7700-2
Frequent Visitor

Comparing metrics broken down by day, against a reference date (D-3, D-2, D-1)

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: "Dia" = "Day""Dia" = "Day"

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:

RefPrevious PeriodTarget Period
D-41.008.739,911.010.373,16
D-3677.959,82746.579,72
D-2962.194,37983.525.86
D-11.151.780,321.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.

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

Hi @7700-2 ,

 

Please create a new table like below and refer to the measure below.

3.PNG

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

Hi, I think I understand your requirements. My suggestion is to create a new, not connected table like this:

New tableNew table

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 😞

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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)

DateRevenue
(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.

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.