Using Two Date Tables To Do Analysis

Hi All,

Hope everyone is staying safe during this time.

I am trying to replicate a report in Power BI. The analysis is aiming to show:

  • Products (main context) total sales for the last 2 months, followed by;
  • Total sales for the last 3 days of the current month;
  • Then some analysis for :
    *VS Previous: compare the last 2 days
    *VS %: % change last 2 days
    *MTD: Last sales for the product VS Last month
    *MTD %: % change
    *Var to Budget: (Total current month - Total budget for the current month)

Sample report view to help illustrate the above

I think I am struggling to build a measure that will work with two different date table, which I know it’s hard. Not sure if it is possible to have 2 date tables on one page? If yes, any workaround to have them in one matrix table? Any suggestions would be highly appreciated.

PBXI file attached. Dashboard Test.pbix (688.0 KB)

Hi @Hesham, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi @Hesham,

Interesting case. However I do have some questions because when I openend your file it also included Date slicers, so do these have to be taken into account?

  • You have Jan 10th selected would you expect Nov & Dec 2019, Jan 8, 9 and 10 to be shown??
  • What needs to happen when the new Month does not yet have 3 dates? Like on Jan 1st or 2nd??
  • Comparison VS Previous: compare the last 2 days How do you want this to be calculated should that only be Jan 8 vs 9? OR …??

Please provide as much detail as you can. Thanks!

Hi @Hesham. Two date tables aren’t needed … as @Melissa said, not sure how to incorporate your date slicers, but here’s a possible solution using a single date table and measure branching that might get you started. Hope it helps. Greg
TEST Dashboard 2.pbix (681.7 KB)

1 Like

Hi @Hesham

It is quite confusing what you want to achieve as @Melissa said but I think @Greg has done a good job with his solution. I haven’t checked the figures but the behaviour works well.

I think it might be useful to provide you with a little bit of info.

The first thing is that it isn’t difficult to use multiple date tables and this is something that can be used in Data Warehouse solutions, however it can make a solution less optimal as you need to use separate slicers for each and it could confuse the users.

In Power BI you can make multiple relationships to a single Date Table but only one of the relationships can be active, this is achievable with DAX and USERELATIONSHIP. There are plenty of resources on Enterprise DNA that explain this. However, all this aside, as @Greg stated, you do not need multiple date tables for this solution.

Note also that when you click on the Date table in your example you have a Hierarchy on the Date field, this is the in built Power BI Date Hierarchy, some people like to use it others don’t, but when you have a Date Table you should turn that off by marking the Date Table as a Date Table in Table Tools. @Greg has marked the date table in his example as such.

Note from @Greg solution that Selection Date and Day Number tables have been deleted, these are not required as the functionality you were trying to produce comes from the Date table supported by the DAX Measures, again, great DAX examples on the Enterprise DNA site. He also named Data to Sales for more clarity.

I hope this helps in your understanding.

John

Hi @Melissa, @Greg and @jock

Thank you all for your time and for the prompt response. Glad to be part of this great community.

@Melissa The general requirement is to do a DAILY analysis of the sales, so when choosing a date (let’s say today 28/4/20) the table would show:
1- Sales 2 months ago from the selected date (so it will show Feb and Mar 20 total sales), and;
2- Last 3 days (Total sales by date for each product), and;
3- The rest of the analysis as in the screenshot (VS previous, MTD,…etc)

@Greg Thank you very much for sharing your solution and its a good starting point for me. The main challenge I was after is to show the Last 3 days in columns as dates (So for example, 10/04/2020, 09/04/2020 and 08/04/2020 of Jan) instead of showing “Sales CM 3rd Last Day”. Maybe it is not possible as of now.
Out of interest, in your “Day Mesure”, do you have any recommendation to get the value of the last 3 data points instead of the last 3 consecutive dates?

@jock Thanks for sharing the summary as I am still exploring eDNA resources. Hopefully, I will be able to build the report and align it with the requirements so I can promote PBI in my organization.

Hesham

Hi @Hesham. I’m not aware of any method of setting the columns names in a Power BI table viaual by a measure. You can easily set a column header in a paginated of SSRS report, but paginated reports have their limitations to, so… As far as last 3 data points vs. last 3 dates, my first thoughts might be to use RANKX or TOPN in the various [Day Measures] to retrieve the desired the 3 data points. Greg

Hi @Hesham,

Apologies for the late reply.

As far as I know @Greg is correct - there is no way of dynamically changing the name of a column (Measure), however you can visualize the values based on your selection. I’ve placed a card visual just above the table to illustrate that.

With the Month & Year and Date Slicer, it all revolves around this type of basic pattern.

Sales 0D ago = 
VAR SelectedDate = COALESCE( SELECTEDVALUE( 'Dates'[Date] ), SELECTEDVALUE( Dates[MonthEnding] ), TODAY())
RETURN

CALCULATE( [Total Sales],
    FILTER( ALL('Dates'[Date]), 'Dates'[Date] = SelectedDate )
)

I hope this is helpful. Here’s the test file.
eDNA - Dashboard Test.pbix (687.3 KB)

1 Like

Hi @Melissa,

Thank you for coming back to me and sharing your solution. Smart move with the Card Visual!

As a way of collaboration, I am sharing one piece of the puzzle regarding showing the Last N “Dates” dynamically in the columns. I understand that there is no way to change the name of the (Measure) dynamically; however, I tried with Dates column instead.

Linking to my initial proposal to have a separate “Selected Dates” table that has no relationship in the model, and taking @Greg hint on using TOPN, here is the dynamic measure to show the last N dates:

Last 3 days = 
var Top3days =CALCULATETABLE(TOPN(3,FILTER(VALUES(Sales[Date]),Sales[Date]<=SELECTEDVALUE('Selected Dates'[Date])),[Date],DESC),ALLSELECTED(Sales)) 
RETURN 
IF(SELECTEDVALUE('Selected Dates'[Date])=BLANK(),MAX('Sales'[Values]),CALCULATE(SUM(Sales[Values]), FILTER(Sales, Sales[Date] in Top3days)))

Output:

Thought I cracked it, but when I apply the same approach for LM & L2M ago, I get multiple column headers which are not ideal for my report. Might give up and use your solution, as it delivered the requirements, though I am sure my stakeholders will challenge it :slight_smile:

Thanks again for your time and help.

Attached is the updated test file.
eDNA - Dashboard Test - Updated.pbix (706.6 KB)