Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 -
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.
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) ))
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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:
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |