PY sales on mtd basis

[Anonymous]

Hi Guys,

I'm using this code to get sales of mar 2021 on mtd basis, but it is not showing values for 1st date I tried this code with every month but the error is same it is not showing values for 1st of every month can anyone help

PY MAR 21 = CALCULATE(SUM(table[sales]), 'Date'[Month Number] = 03 && 'Date'[Year] = 2021 && (DAY('Date'[Day])<DAY(TODAY()-1)))

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Is that 1st of each month have no values? There some mistakes in your code. 

PY MAR 21 = CALCULATE(SUM(table[sales]), 'Date'[Month Number] = 03 && 'Date'[Year] = 2021 && (DAY('Date'[Day])<DAY(TODAY()-1)))

1 DAY(<date>).

This function's parameter need a date, but 'Date'[Day] is a number. You should use 'Date'[Day] directly without DAY(). For example, 'Date'[Day] is 1,then DAY(1) result is 31, 31 is impossible less then DAY(TODAY()-1) even if today is 31, so the sale on 1st day has been removed form the calculate table.

2 Try change "<" to "<=" in the last filter of this CALCULATE function.

Beause if Today() is 2, result for DAY(TODAY()-1) is 1, so 'Date'[Day] should less then 1, so range of the result is (-∞，0]. 

code should like the following:

PY MAR 21 =
CALCULATE(
    SUM( table[sales] ),
    'Date'[Month Number] = 03
        && 'Date'[Year] = 2021
        && 'Date'[Day] <= DAY( TODAY() - 1 )
)

I do not sure it is right dut to i have no idea for your Date[Day] type and where this code used, measure or column?

Or you can share some example data if you need more help.

Best Regards

Community Support Team _ chenwu zhu

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