Calculate if data presents in all day in a month.

[Anonymous]

All,

    Trying find this is Dax, if sales exists in every day of a month, then that month is 'Completed', if not every day in a month then 'Not complete'. Issue is how to check the every day logic in DAX, even if 1 day is missed in a motth. I have dim date and fact_sales in simple term.

So, if users pick date slicer of  01/01/2021-03/31/2021 and two missing sales one in Feb and one day in March, then January would be complete but February and March would be incomplete. Here is how is the raw data looks.

Date|Sales

04/01/2021|200

04/02/2021|400

Here since not all days for April exists, Month of April sales would be incomplete

[Anonymous]

Thanks. Worked well.

[bcdobbs]

Have a look at this demo solution: Period Completion Demo 

[bcdobbs · Accepted solution]

Notes on demo solution:

1) You need a date table, I built this one in DAX:

Date = 
    ADDCOLUMNS (
        CALENDAR( DATE(2021,01,01), DATE(2021,12,31) ),
        "Year Number", YEAR([Date]),
        "Month Number", MONTH([Date]),
        "Month Year", DATE(YEAR([Date]), MONTH([Date]), 1)  // Format this as mmmm yyyy
    )

Month Year is actually a start of month date but if you format it in power bi as mmmm yyyy you get January 2021 displayed and sort order is taken care of.

Use the mark as date table option.

2) Create a relationship to Sales. I've used an example table like yours which will default to trying to be a 1 to 1 relationship. Change the relationship type to be 1 to many with the date table on the one side and a single filter direction.

3) Add a calculated column to the Date table which flags if a day has no sales:

NoSales = 
    IF (
         CALCULATE( SUM( Sales[Sales] ) ) > 0,
        0,
        1
    )

CALCULATE is there to force a context transition from the row you're on into the filter context. You could use MAX in your case but SUM is more future proofed if your Sales table had multiple rows per day.

4) Create a Period Completion measure:

Period Copmletion = 
    VAR DaysWithoutSales =
        CALCULATE(
            SUM('Date'[NoSales]),
            REMOVEFILTERS('Date'),
            VALUES('Date'[Month Year]) //Ensures whole month is included
        )

    RETURN 
        IF(DaysWithoutSales = 0, "Complete", "Not Complete")

This just adds up the NoSales flags in the date table. However first it removes any existing filters on the date table and only puts back whole month filters.

5) You can then use this safely in a matrix:

If I'd had some coffee I'd try and write it without the calcualted column in the date table but I think it's a reasonably elegant solution.

[AlexisOlson]

I've had my coffee, so I'd suggest that if you want it to work with periods other than months (say weeks or quarters) and/or work with slicers and other filters, I'd recommend making the DaysWithoutSales part more dynamic like this:

DaysWithoutSales =
COUNTROWS (
    FILTER (
        VALUES ( 'Date'[Date] ),
        CALCULATE ( SUM ( Sales[Sales] ) ) = 0
    )
)