How to fill missing dates

[abhishekrws01]

Hello - I am looking for some help. 

I have the following three tables in the model:

1. Calendar table - has all the dates

2. DailyTxdataSQL - Fact table recording sales by day and country.

3. Region - has a mapping of the country, display country (group of smaller countries), etc.

I am trying to create a table where I would like to k now which country has how many days in a week/month/year, etc.

The DAX formula below works perfectly when there is a daily sale record. But it doesn't work when a country doesn't have a sales record (due to a system or data issue). For Example, if there were no sales on 01-01-2023 and it was a working day, the row is not created. I would like to create a row and count it in the working day. (Note: DailyTxdataSQL tables have a column [Weekdays] which is flagged as 0 and 1) 0 means holiday or weekend, and 1 means working day. 

Thank you in advance.. regards, 

[Anonymous]

Hi @abhishekrws01 ,
Based on the information you provided, you can try to solve the problem with the dax below:

Working Days Count = 
CALCULATE(
    COUNTROWS(Calendar),
    Calendar[Date] >= MIN(DailyTxdataSQL[Date]),
    Calendar[Date] <= MAX(DailyTxdataSQL[Date]),
    Calendar[Weekdays] = 1,
    NOT(ISBLANK(DailyTxdataSQL[Country])),
    ALLEXCEPT(DailyTxdataSQL, DailyTxdataSQL[Country])
)

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AmiraBedh]

Try the following : 

Working Day_Table = 
SUMMARIZE(
    ADDCOLUMNS(
        CROSSJOIN(
            'Calendar',
            Region
        ),
        "DailyTxData", RELATED(DailyTxDataSQL[Weekdays])
    ),
    'Calendar'[Date],
    Region[Country],
    Region[Display Country Name],
    "Working Days", IF(ISBLANK(MAX([DailyTxData])), 0, MAX([DailyTxData]))
)

[abhishekrws01]

Hi - Thank you for looking into it. I get the following error.

Thanks