Forum Discussion

abhishekrws01's avatar
abhishekrws01
Helper I
2 years ago

How to fill missing dates

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, 

  

 

Need Help

3 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    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's avatar
      abhishekrws01
      Helper I
      2 years ago

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

      Thanks

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.