cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## PY sales on mtd basis

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)))
1 ACCEPTED SOLUTION
Community Support

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 31so 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.

Community Support

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 31so 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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors