Showing results for 
Search instead for 
Did you mean: 
New Member

Turnovers formulas

I would need to make a clustered column chart, on the x-axis it would need to show department and for each department- two columns-Y-axis - turnover yesterday2021 and turnover yesterday2022. For the turnover I have formula- amount * sale price. But i'm not sure how to write formulas for turnovers - yesterday2021 and yesterday2022. I have wrote something like this - 

2022yesterdayturnover = CALCULATE(SUM(Query1[turnover]), DATE(YEAR(NOW()-1, MONTH(NOW(), DAY(NOW()-1)) )))
But it shows an error- Too many arguments were passed to the MONTH function. The maximum argument count for the function is 1. 
I have made calendar table as well-
Calendar 1 =
VAR Days = CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2022, 12, 31 ) )
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"Year Month Number", YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1,
"Year Month", FORMAT ( [Date], "mmm yy" ),
"WeekNum" , WEEKNUM([Date])
I have a big amount of data which comes from Postgresql.
New Member

Unfortunately, the information is sensitive. And it's not in English.

I will try to explain it to you.

I have two tables - Calendar 1 (used formula above to make one) and Query1 (data from Postgresql).

The Query1 table contains columns- date, product id, product name, amount, price etc.

There is a formula for turnover- price * amount. 

I have made one relationship between tables Query 1 [date] and Calendar 1 [date] - type many to many.

The rows in Query 1 are not unique.

Super User
Super User

Hi @niksdu ,


The error is because of the parenteses the formula need to be:


2022yesterdayturnover =
    SUM ( Query1[turnover] ),
    DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () - 1 ) )


However looking at your formula this will not give you the previous day metric because when you change month it will not return the correct value try the following:




2022yesterdayturnover =

var PreviousDayValue = TODAY() -1 
    SUM ( Query1[turnover] ),
   Table[Date]= DATE(YEAR(PreviousDayValue)-1, MONTH(PreviousDayValue), DAY(PreviousDayValue) ))


Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Thank you for your solution.

The formula is working, however the numbers doesn't match with the numbers on database.


Also, I haven't figured out correct formulas for yesterday 2021 turnover, last month 2021 turnover and last month 2022 turnover.

For now, I have formulas like this-


Yesterday 2021 turnover:

2021yesterdayturnover = var yesterday2021turnover = TODAY() -1 Return
CALCULATE (SUM(Query1[Turnover]), 'Calendar 1'[Date]= DATE(YEAR(yesterday2021turnover)-1, MONTH(yesterday2021turnover), DAY(yesterday2021turnover)-1 ))
The numbers doesn't match with the numbers on database
Last month 2021 turnover:
2021lastmonthturnover = var lastmonth2021turnover = TODAY() -1 Return CALCULATE(SUM(Query1[Turnover]),'Calendar 1'[Date]= DATE(YEAR(lastmonth2021turnover)-1, MONTH(lastmonth2021turnover)-1, DAY(lastmonth2021turnover)))
The formula outputs numbers, but they're too small, seems like for one day.
Last month 2022 turnover:
2022lastmonthturnover = var lastmonth2022turnover = TODAY() -1 Return CALCULATE(SUM(Query1[Turnover]),'Calendar 1'[Date]= DATE(YEAR(lastmonth2022turnover), MONTH(lastmonth2022turnover)-1, DAY(lastmonth2022turnover) ))
The formula outputs numbers, but they're too small, seems like for one day.

Hi @niksdu .


Without any data is difficult to give you a best answer, I just did the metrics based on the ones you already provided.


Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors