Reflecting Weekly Consecutive Dates

[Anonymous]

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 Year	Date	Product_Schedule	Product_Sales_Count.Weekly
FW05-2022	Tuesday, September 28, 2021	Weekly	3
FW02-2022	Tuesday, September 07, 2021	Weekly	2

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

Fiscal Week Year	Date	Product_Schedule	Product_Sales_Count.Weekly
FW05-2022	Tuesday, September 28, 2021	Weekly	3
FW04-2022	Tuesday, September 21, 2021	Weekly	0
FW03-2022	Tuesday, September 14, 2021	Weekly	0
FW02-2022	Tuesday, September 07, 2021	Weekly	2

[amitchandak · Accepted solution]

@Anonymous , You can create a date table in power query and also create week start date and then merge these two tables on week start or end date

Date Table in Power Query: https://youtu.be/so_A22HXbwM

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Anonymous]

@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! 

[Anonymous]

@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.