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 am creating a report of gallons sold for a product sold in bulk by the gallon and also sold in containers of various sizes. I need the total of gallons sold for all bulk and in each container size.
I am looking at the current month’s sales.
A multiplier is used to determine the gallons contained in each size of cylinder.
This is a simple calculation in a Calculated Column:
Total Gallons = Sales[Qty] * Sales[multiplier]
I also am performing the same calculation for the gallons sold during the same period in the previous year.
Here is the measure for last year’s sales:
Last Years Sales = CALCULATE(Sales[Qty]), DATEADD('Date'[Date], -1, YEAR))
However, this only gives me the Qty Sold of bulk and the number of each container sold. It does not give me the gallons sold in each container size. I am not able to create a Measure or Calculated Column for this that doesn’t error out.
Anyone see a way onward through the fog?
Thanks
Solved! Go to Solution.
Hi, @BudMan512
Please refer to similar thread.
You should make sure you have added a calendar table with consecutive dates.
Then you can try formula like:
Last Years Sales = CALCULATE(Sales[Qty], SAMEPERIODLASTYEAR('Calendar'[Date]))
or
Last Years Sales = CALCULATE(Sales[Qty], PREVIOUSYEAR('Calendar'[Date]))
Best Regards,
Community Support Team _ Eason
Hi, @BudMan512
Please refer to similar thread.
You should make sure you have added a calendar table with consecutive dates.
Then you can try formula like:
Last Years Sales = CALCULATE(Sales[Qty], SAMEPERIODLASTYEAR('Calendar'[Date]))
or
Last Years Sales = CALCULATE(Sales[Qty], PREVIOUSYEAR('Calendar'[Date]))
Best Regards,
Community Support Team _ Eason
Here is what ended up working for me.
FY22 = (Calculate(SUMX(Sales,Sales[QTY] * Sales[INV_MULTIPLIER])))
FY21 = CALCULATE( [FY22], DATEADD('Date FY22'[Date FY22],-1,YEAR))
Thanks everyone who offered advice.
Gallons last year = CALCULATE( SUMX( 'Sales', 'Sales'[Qty] * 'Sales'[Multiplier]), DATEADD('Date'[Date], -1, YEAR) )
Thanks Johnt75. The good news is the formula did not error out. But, when added to my table it did not render any results.
Are 'Sales'[Qty] and 'Sales'[Multiplier] columns or measures? My intention was to use the columns from the Sales table. If they are measures, can you post the definitions?
They are columns
OK, can you grab the DAX query used to generate your table visual from Performance Analyzer and post that?
Hi,
Share the link from where i can download your PBI file.
Thank you for responding Ashish, but I am not allowed to share customer data.
I think you need to add the year slicer . if you select 2022, then will return you the value of 2021.
Could you pls provide the sample data and expected output?
Proud to be a Super User!
Thanks Ryan, I am using one slicer for date range of the current year. And then the 'Calculate' function for the previous year. Is there a way to use
Last Years Sales = CALCULATE(Sales[Qty]), DATEADD('Date'[Date], -1, YEAR)) * (sales[multiplier)
or some variation there of? So far I haven't found none.
As mentioned previously I am not able to share the data. Thanks for the reply.
it's better to share the sample data, you can remove sensitive data or create some dummy data and the expected output based on the data you provided.
Proud to be a Super User!
Thank you Ryan. I got called away unexpectedly Friday and will be off most of this week. When I return I will create some dummy data to share. Thank you everyone for the help.
Clarification, the slicer is set for the current month to date and the previous year calculation is for the same MTD.
Covering 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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |