Can i fix data to one month in DAX calculation?

[Anonymous]

Hello Everyone

Hope all are doing fine

Apologies in advance, if it was a simple solution and I couldn't figure out, also I have searched the whole forum but couldn't find an exact solution to my problem. 

My data set consists of data as :

only Dec data for 2017,2018,2019

and then March data for 2018,2019,2020

I have some calculations which I want them to fix the current period calculation to whole last period, For example

Page level filters are: Year 2020 and Month March

I want to show current period gross margin  

so my formula would be = 

GrossProfit Margin = VAR GM = DIVIDE(SUM('Segment Data YTD'[Sales]) + sum('Segment Data YTD'[Selling Cost]) + SUM('Segment Data YTD'[COGS]), SUM('Segment Data YTD'[Sales]),0)

RETURN CALCULATE(GM,ALL('Date'[Date]))

Now I want to show Gross Margin of last year  - Year 2019 and month Dec in the next column of the table. - Right now when i am trying its aggregating 2019 March and 2019 Dec and shows me the result which is not right. 

Then I want to show Avg Price per unit;

my formula is:

Avg.PricePerUnit = DIVIDE(SUM('Segment Data YTD'[Sales]),SUM('Segment Data YTD'[Sales Volume in BN]),0)

avg price per unit YA = CALCULATE([Avg.PricePerUnit],PARALLELPERIOD('Date'[Date],-1,year))

avg price per unit % = (divide([Avg.PricePerUnit],[avg price per unit YA],0)-1) -- the result which i want to show in my table. 

so right now, again Power BI is aggregating the 2019 March and 2019 Dec Sales and Volume and gives me incorrect Avg price per unit for last year. 

I want to fix this to Dec 2019 only in a way that when the page-level filter is selected to 2019 March  - it calculates to Dec 2019 March vs 2018 Dec and so on. 

So this is how i want my table to look like. Is there any way?

Thank You in advance

Regards

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[amitchandak]

You can try a year trailing measure or datesytd as per need

Year behind Sales = CALCULATE([Avg.PricePerUnit],dateadd(Table[Date],-1,Year))

YTD Sales = CALCULATE([Avg.PricePerUnit],DATESYTD((Table[Date]),"12/31"))
This Sales = CALCULATE([Avg.PricePerUnit],DATESYTD((ENDOFYEAR(Table[Date])),"12/31"))

Last YTD Sales = CALCULATE([Avg.PricePerUnit],DATESYTD(dateadd(Table[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE([Avg.PricePerUnit],DATESYTD(ENDOFYEAR(dateadd(Table[Date],-1,Year)),"12/31"))

Last to last YTD Sales = CALCULATE([Avg.PricePerUnit],DATESYTD(dateadd(Table[Date],-2,Year),"12/31"))

Try this like

avg price per unit % = divide([Avg.PricePerUnit]-[avg price per unit YA],[avg price per unit YA])

[Anonymous]

Thank You @amitchandak 

But for this year sales - I want it to calculate the current period selected like for example If I have a filter of March on page, the calculation should be March sales/March Volume - and that should divide by Dec 2019 Sales/Dec 2019 Volume.