Show All Dates Weekly Dates With or Without Transactions

[win_anthony]

The goal is to track the weekly (or monthly) sales transactions. I have a fact table which has transactions along with a Date Dimension table. The relationship between the two tables are the transaction date and date. Within that fact table, I was able to create a calculated column flagging which products were sold Daily / Weekly / Monthly. My problem is being able to accurately create a table which will show dates on either a Weekly or Monthly cadance along with reflecting any weeks or months that does not have any sales transactions. Any advice will be greatly appreciated. 

Below you will find: current measure / current output / expected output

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

CALCULATE(
    [Product_Sales_Count] ,
    'FactTable'[Product_SCHEDULE] = "Weekly"
)

Current Output: Only reflects weeks that have sales. Goal is to reflect consecutive weeks regardless if there are sales or not.

Product_Sales_Count	Date
1	1-Sep-21
2	13-Sep-21
3	27-Sep-21

Expected Output: Notice weeks Sept 6, 2021 + Sept 20, 2021 > The expected output is to reflect both weeks which does not record sales along with weeks that do have sales

Product_Sales_Count	Date
1	1-Sep-21
0	6-Sep-21
2	13-Sep-21
0	20-Sep-21
3	27-Sep-21

[win_anthony]

@v-jingzhangWould you be so kind as to provide advice on how I can create a table in which continuous dates can be highlighted on both a weekly and monthly cadance? I was able to find a workaround in which continuous consecutive daily dates can be displayed on a table by using the below listed measure. The goal is to do the same but on a weekly and monthly continuous consecutive cadance. Problem is the below measure works well for consecutive daily dates, not for weekly or monthly dates. Please let me know your thoughts. 

Should you require additional information, please do not hesitate to let me know. Thank you

Sample Data via GitHub 

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

[win_anthony]

@Syndicate_Admin Hello. Would you be so kind as to provide advice on how I can create a table in which continuous dates can be highlighted on both a weekly and monthly cadance? I was able to find a workaround in which continuous consecutive daily dates can be displayed on a table by using the below listed measure. The goal is to do the same but on a weekly and monthly continuous consecutive cadance. Problem is the below measure works well for consecutive daily dates, not for weekly or monthly dates. Please let me know your thoughts. 

Sample Data via GitHub 

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

[win_anthony]

@Icey This is in conjunction with the previous response you provided. I was able to find a workaround where I can find the continuous daily transactions regardless if there were any sales or not. My current problem is to be able to create a table where the Weekly or Monthly cadance of dates are to be reflected. Would you be so kind as to provide any advice on how I can achieve the desired output? Below is a link to sample data along with expected output on GitHub. Please let me know your thoughts. 

Sample Data on GitHub 

[mahoneypat]

There is probably a better way to approach your needed functionality without adding "Weekly" column to your fact table.  Not totally clear on why you are doing that.  In any case, did you try just using "+ 0" to your expression, so you see a value on every row?

Pat 

[win_anthony]

@mahoneypat Thank you so much for your support. Apologies but I am confused on your suggestion of adding "+0" to my expression. Below are my 2 measure that I currently have along with sample data. Please let me know your thoughts on how I can update my expression or any advice on how I can achieve the expected output. 

[Product_Sales_Count] measure: 

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

[Product_Sales_Count.Weekly] measure:

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

Sample Data via GitHub 

[VahidDM]

Hi @win_anthony 

Can you share a sample of your data with [Product_Sales_Count] measure codes?

Appreciate your Kudos!!

[win_anthony]

@VahidDM Thank you so much for your support. Below is sample data along with measure + expected output. 

Please let me know your thoughts. 

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

Sample Data via GitHub