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