Turnovers formulas

[niksdu]

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 ) )

RETURN ADDCOLUMNS (

Days,

"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.

[niksdu]

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.

[MFelix]

Hi @niksdu ,

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

2022yesterdayturnover =
CALCULATE (
    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 
Return
CALCULATE (
    SUM ( Query1[turnover] ),
   Table[Date]= DATE(YEAR(PreviousDayValue)-1, MONTH(PreviousDayValue), DAY(PreviousDayValue) ))
)

[niksdu]

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.

[MFelix]

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.