Display active Event and Sum totals for Event during that period

[addlahta]

Hello All,

I have two tables, one has Event with start and End Date. The other is my fact table with dates and weekending. 
They have a relationship is the Stock code that is shared between both. 

I want to be able to display the sum of Sales Inc, based on a week ending slicer. and the values change based on the Event that was running. Let me know if you need more information to be able to help me solve this

I have tried the below to get the active event and use that in the table:

[Anonymous]

If thats the case you can connect the "End Date" column from the Promotions Table to the newly created Calendar Table "Date" Column and it should work. 


 

[addlahta]

Then the issue is its not linked to the Sales Data - only the date. The Relationship is inactive when i try and link both. The reason i need it linked is becase of the product data in the sales table i need to see for each promo. If its not possible i understand
Relationships are as below (i already had the calendar table). Could you us

[Anonymous]

Hi 

@addlahta , Yes this is possible. To activate inactive relationship we can use the USERELATIONSHIP function.  In your case, if you want to get the total sales for active promo:

New Sales Measure = 

CALCULATE(
     SUM([SalesInc]), 
       USERELATIONSHIP(Calendar[Date], 
                       PromotionsTable[EndDate])
)

Hope that helps.

[addlahta]

This still isnt working.

Im not sure Ive im conveying the information i want to properly

I only what to show promos that appeared in the week ending selected. e.g. Weekending 28/08/2022 would be the 22/08 to 22/08

End point is to have a table like this in PowerBI :

Department Product Group and Brand are all contained in a Product Table that links to the sales table by SKU.

Sales Table Links to Calendar table by Date

Promo Table links to Sales Table Via SKU - I think one of the issues here is that one SKU good be in multiple promotions with a different start date

Calendar is generated by PowerBi as a DateTable

This is my data model:

[Anonymous]

Hi @addlahta ,

Are you able to remove any sensitive data in your pbi file and attach it here. Would help if we could have a look at the file. 

BR

Dax_noob

[addlahta]

Hi,

Ive created another Pbix file with the same idea in it with all the sensitive data removed.
I think ive recreated it enough:
https://drive.google.com/file/d/1j-i1AmqfCRZcu5NkZZFUGgSOqD2WKOMy/view?usp=sharing

[Anonymous · Accepted solution]

Hi @addlahta 

Try this
https://drive.google.com/file/d/1ACy1O0GmSa4cgTLpBVqM7JmX410WAV0W/view?usp=sharing

Regards

[addlahta]

It ended up being the most stupid of things... i just need to use the week ending date in the Promotion Table to get what I wanted.... Tweaked some of my measures and its all working now.

Thanks for being the sounding board and offering your suggestions!

[addlahta]

Unfortunately not - its all linked via database.

I can possibly create a dummy one, just takes a lot of time i dont have at the moment...if i do it ill post it here

[Anonymous]

Hi 

@addlahta , sorry could you provide more details?

From the screenshot, do you want to create a dynamic slicer which shows the data for that whole week ending?

BR

Dax_Noob

[addlahta]

Yes - so if the week ending is 28/08 - then it will be all dates from the 22/08 to 28/08

[Anonymous]

Hi @addlahta 

I would suggest you to have a calendar table.

Step 1: Click Home > New Table > Create a Calendar Table

Calendar  = CALENDAR(MIN('Sales Data'[Date]),MAX('Sales Data'[Date]))

 
Step 2: Create a new calculated column in the new table

Week Num = WEEKNUM('Calendar'[Date],2)

 
Step 3: Create another calculated column in the same table

Week Ending = 'Calendar'[Date] - WEEKDAY('Calendar'[Date],2)+7

Step 4 : Now connect in the data model the 'Calendar'[Date] column with the 'Sales'[Date] column. 

Step 5: Move the newly created "Week ending" column from the calendar table to a filter and try it now.


BR

Dax_Noob

[addlahta]

the problem with that is that it will bring in events that have expired already - and attach sales data to it bases on what stock codes are in the event that had sales in that period.

E.g. if one event finished on the 09/07/2022 but had stock codes in their that we on sales on the weekend ending i selected (e.g. 28/06/2022) then it will have sales against that event.

Alternative I was looking at a measure for Active Promo - so will only show event that were had a start date during the week that i have selected week ending for e.g. if i selected 28/08/2022 it would only just events that started in the week 22/08 to 28/08