Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Reflecting Weekly Consecutive Dates

The goal is to have a table reflect transactions on a consecutive weekly (or monthly) track highlighting the number of transactions made during the week. There is a calendar dimension table which is linked to the fact table on when the transaction was initially created. I succesfully created a daily table which will show dates in a consecutive order regardless if there was a transaction made or not. Problem now is that I am struggling with creating a table that will follow a weekly or monthly cadance.

 

Below you will find current measure producing the count of transactions / current measure distinguishing the weekly cadance / sample data / expected output

 

Your advice and support is greatly appreciated. 

 

EDIT: I tried the below measure to fill in 0's for dates which does not have sales transactions. This works well when I apply it to products that sell on a Daily cadance. But does not work well for products that sell on a Weekly or Monthly cadance as it will output daily consecutive dates. 

 

 

IF(
    [Product_Sales_Count] = BLANK(),
    0,
    [Product_Sales_Count]
)

 

 

Current Measure producing count of transactions = [Product_Sales_Count]

 

 

[Product_Sales_Count] = 
CALCULATE(
    COUNT( 'FactTable'[Transaction_ID] )
	,FILTER(
		FactTable,
		FactTable[IsLastTransaction] = 1 --This is a flag to highlight the customer's latest transaction 
	)
)

 

 

Current Measure distinguishing the weekly cadance = [Product_Sales_Count.Weekly]

 

 

CALCULATE(
    [Product_Sales_Count]
    ,FactTable[Product_Schedule] = "Weekly"
)

 

 

Sample Data: Notice the jump in transactions between FW02-2022 > FW05-2022

Fiscal Week YearDateProduct_ScheduleProduct_Sales_Count.Weekly
FW05-2022Tuesday, September 28, 2021Weekly3
FW02-2022Tuesday, September 07, 2021Weekly2

Expected Output: Notice that FW03-2022 + FW04-2022 is included to reflect 0 transactions for the week. This is the goal. 

Fiscal Week YearDateProduct_ScheduleProduct_Sales_Count.Weekly
FW05-2022Tuesday, September 28, 2021Weekly3
FW04-2022Tuesday, September 21, 2021Weekly0
FW03-2022Tuesday, September 14, 2021Weekly0
FW02-2022Tuesday, September 07, 2021Weekly2

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      amitchandak Thank you so much for your support. I just watched your video and will try to implement your advice. Just so I am clear on your advice, you are suggesting that I create a 2nd calendar table? What if my current Calendar dimension table already has Start/End Week? My sincerest apologies but I am confused on what you are advising. 

    • Anonymous's avatar
      Anonymous
      Not applicable

      amitchandak after a bit of work and testing, your advice served to work well! Thank you so much for the advice! Much gratitude for your support!