Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pbihello
Regular Visitor

Calculate if data presents in all day in a month.

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

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

bcdobbs_0-1639211565153.png

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:

 

bcdobbs_1-1639211796432.png

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

4 REPLIES 4
pbihello
Regular Visitor

Thanks. Worked well.

bcdobbs
Super User
Super User

Have a look at this demo solution: Period Completion Demo 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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.

bcdobbs_0-1639211565153.png

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:

 

bcdobbs_1-1639211796432.png

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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
    )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.