Power BI Dax Measure to calculate total sum of errors on the Prev day of everyday in current month

[BOti62]

Hi all

I am trying to create a dax formula which lets me create the below table in power bi but have come across issues. 

This is the measure I've tried to use but it does not work:

 PreviousDayToStartOfMonthErrors = VAR CurrentDate = TODAY() VAR StartOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1) VAR PreviousDay = CurrentDate - 1 // 

RETURN CALCULATE( SUMX( DISTINCT(DataQualityDashboard[RuleImplementationID]),

CALCULATE( SUM(DataQualityDashboard[ErrorCount]), DataQualityDashboard[DateStamp] >= DATE(YEAR(StartOfMonth), MONTH(StartOfMonth), DAY(StartOfMonth) - 1) && DataQualityDashboard[DateStamp] <= PreviousDay ) ) )

Date	Errors Month To Date	Previous Day
01-Jan-24	505278
02-Jan-24	505678	505278
03-Jan-24	505245	505678
04-Jan-24	503607	505245

Is anyone able to help please?

[lbendlin · Accepted solution]

This seems to be missing some important details. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Consider using OFFSET -1

[Anonymous · Accepted solution]

Hi @BOti62 ,

Not being able to fully understand your needs based only on the information you provided, I tried to modify the DAX a bit:

PreviousDayToStartOfMonthErrors =
VAR CurrentDate = TODAY()
VAR StartOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)
VAR PreviousDay = CurrentDate - 1
RETURN
CALCULATE(
    SUM(DataQualityDashboard[ErrorCount]),
    FILTER(
        DataQualityDashboard,
        DataQualityDashboard[DateStamp] >= StartOfMonth &&
        DataQualityDashboard[DateStamp] <= PreviousDay
    )
)

If this is still not possible, please provide the full sample data and data model, as well as example screenshots of the results you would like to get, thank you!

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

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual/filter/slicer, drag Date from the Calendar Table.  Write these measures:

Errors = counta(Data[Error])

Errors on previous day = calculate([errors],previousday(calendar[date]))

Hope this helps.

[Anonymous · Accepted solution]

Hi @BOti62 ,

Not being able to fully understand your needs based only on the information you provided, I tried to modify the DAX a bit:

PreviousDayToStartOfMonthErrors =
VAR CurrentDate = TODAY()
VAR StartOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)
VAR PreviousDay = CurrentDate - 1
RETURN
CALCULATE(
    SUM(DataQualityDashboard[ErrorCount]),
    FILTER(
        DataQualityDashboard,
        DataQualityDashboard[DateStamp] >= StartOfMonth &&
        DataQualityDashboard[DateStamp] <= PreviousDay
    )
)

If this is still not possible, please provide the full sample data and data model, as well as example screenshots of the results you would like to get, thank you!

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

[lbendlin · Accepted solution]

This seems to be missing some important details. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Consider using OFFSET -1