Daily monitoring (Calendar date)

[Eryka90]

Hi All,

I'm preparing a dashboard for daily monitoring on the invoice payment based on the invoice due date.

Here is samply of my data:

Notes: Status are compare with today date (11/10/2023)

Invoice No	Net Due Date	Status
PLSMAUS020027981	11/6/2023	Pass Due
PLSMAUS020027981	11/7/2023	Pass Due
US10009470	11/8/2023	Pass Due
CBNP02JAN23	11/9/2023	Pass Due
AC0303MAR23	11/10/2023	Today Due
CBNP02JAN23	11/11/2023	Not Due
AC0303MAR23	11/12/2023	Not Due
CBNP02JAN23	11/13/2023	Not Due
AC0303MAR23	11/14/2023	Not Due
CBNP02JAN23	11/15/2023	Not Due
AC0303MAR23	11/16/2023	Not Due
CBNP02JAN23	11/17/2023	Not Due

I want to display the cumulative status for daily monitoring. Example for date 11/10/2023, there will be data for pass due (4), today due (1), Not due (7).

I create new table for calendar and link with invoice table (due date). However, its give me the chart like this:

I want the chart to be like this 

	11/10/2023	11/11/2023	11/12/2023	11/13/2023	11/14/2023	11/15/2023	11/16/2023	11/17/2023	11/18/2023	11/19/2023	11/20/2023	11/21/2023
Pass Due	0	1	2	1	4	2	6	4	1	3	5	8
Today Due	1	1	1	1	1	1	1	1	1	1	1	1
Not Due	11	10	9	8	7	6	5	4	3	2	1	0

Can anyone assist me how to create the DAX and what the tips to make it possible.

Thank you.

[Anonymous]

Hi @Eryka90 ,

Please try to create measure with below dax formula:

Table 2 = CALENDAR(FIRSTDATE('Table'[Net Due Date]),LASTDATE('Table'[Net Due Date]))

Not Due =
VAR _a =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Net Due Date] = _a && [Status] = "Not Due" )
RETURN
    COUNTROWS ( tmp )

Pass Due =
VAR _a =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Net Due Date] = _a && [Status] = "Pass Due" )
RETURN
    COUNTROWS ( tmp )

Today Due =
VAR _a =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Net Due Date] = _a && [Status] = "Today Due" )
RETURN
    COUNTROWS ( tmp )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Eryka90]

Hi @Anonymous ,

I did add your Dax in my PBI report. However its only calculate for Not Due.

I think the main reason is because of DAX on status give me this result as i use to calculate the status as below:

New_Due Status =
IF(ISBLANK('Pending Items'[Net Due Date]), "Due Date Blank",
IF('Pending Items'[Net Due Date] > TODAY(), "Not Due",
IF('Pending Items'[Net Due Date] < TODAY(), "Past Due", "Due Today")

The status information is not in my data at first place.

Here is sample data : https://docs.google.com/spreadsheets/d/1KvWVo_gv4VYO4ShogCGEGI9s840mYjLw/edit?usp=drive_link&ouid=107780509465376624533&rtpof=true&sd=true

[Anonymous]

Hi @Eryka90 ，

It's perfectly fine to display it this way, it's according to your data, the bars aren't stacked up because your status are dynamically generated based on the date, and then you use the date as the x-axis, so every date must have only one status data.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Eryka90]

Hi @Anonymous ,

This is not meet the purpose since I want to monitor on the invoice payment daily. Therefore, by looking at my chart. I should know how many invoice will be due today, not due and already pass due date. I'm not sure whether the x-axis date should be related with due date or not.

Regards

Eryka

[Anonymous]

Hi @Eryka90 ,

Please create measures with below dax formula:

Not Due =
VAR _a =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Net Due Date] <= _a && [New_Due Status] = "Not Due" )
RETURN
    COUNTROWS ( tmp )

Pass Due =
VAR _a =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        [Net Due Date] <= _a
            && [New_Due Status] = "Past Due"
    )
RETURN
    COUNTROWS ( tmp )

Today Due =
VAR _a =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        [Net Due Date] <= _a
            && [New_Due Status] = "Due Today"
    )
RETURN
    COUNTROWS ( tmp )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Eryka90]

Hi @Anonymous ,

Thanks for your assistance. Your DAX perfectly working fine for Today Due and Not Due. 

However, I'm still stuck at Pass Due. Your DAX provided wrong value on the chart. Since I'm still beginner, I dont have clear picture how its should be calculate.

Here is example

The value for pass due should follow the value as in table by daily, not the total.

And also i have few grouping on the data. By choosing selected filter, the data should be reflect well. However its not reflect on my chart.

Appreciate if you can advise more.

Thank you.

Here is the link of Daily Pending Item 

[Eryka90]

Hi Rupak,

I think this is related with DAX since i'm use 

New_Due Status =
IF(ISBLANK('Pending Items'[Net Due Date]), "Due Date Blank",
IF('Pending Items'[Net Due Date] > TODAY(), "Not Due",
IF('Pending Items'[Net Due Date] < TODAY(), "Past Due", "Due Today")
)
)

so that my data become like this

This will not resolve my problem by using staked column chart since the data not ovelapping.

I'm looking for way to monitor based on status (Due today, not due, pass due) for each date.

[Rupak_bi]

Hi,

Use Staked Column chart -> Put date in X Axis --> Count of Invoice No in Y Axis  --> Put Status column in Legend.

Your job will be done

Thanks