Convert different sized containers sold last year to gallons sold

[BudMan512]

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

[v-easonf-msft · Accepted solution]

Hi, @BudMan512 

Please refer to similar thread.

DAX Last Year Sales 

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

[BudMan512]

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.

[johnt75]

Gallons last year = CALCULATE( SUMX( 'Sales', 'Sales'[Qty] * 'Sales'[Multiplier]), DATEADD('Date'[Date], -1, YEAR) )

[BudMan512]

Thanks Johnt75. The good news is the formula did not error out.  But, when added to my table it did not render any results.

[johnt75]

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?

[BudMan512]

They are columns

[johnt75]

OK, can you grab the DAX query used to generate your table visual from Performance Analyzer and post that? 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[BudMan512]

Thank you for responding Ashish, but I am not allowed to share customer data.

[ryan_mayu]

@BudMan512 

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?

[BudMan512]

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.

[ryan_mayu]

@BudMan512 

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.

[BudMan512]

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.

[BudMan512]

Clarification, the slicer is set for the current month to date and the previous year calculation is for the same MTD.