Forum Discussion

niksdu's avatar
niksdu
New Member
4 years ago

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 ...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
4 years ago

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's avatar
niksdu
New Member
4 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    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.