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 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.
Need Help

4 Replies

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

  • niksdu's avatar
    niksdu
    New Member
    4 years ago

    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.