Create fixed measure

[admin_xlsior]

Hi guys,

I'm trying to create a measure, Total sales last year divide by 12, 

I already have my Total Sales measure, so I think I just add new measure and use the function SAMEPERIODLASTYEAR like this : 

Total Sales LY = CALCULATE([Total sales], SAMEPERIODLASTYEAR('Date'[datekey]))

and then another measure for the division :

MyMeasure = DIVIDE([Total sales LY], 12, 0)

But it turns out if I display my report monthly, it divided by 12 from my Total sales in its individual months, so lets say I total sales LY, MyMeasure ( x ) will show like below :

	Total sales	x
Jan-18	1200	100
Feb-18	2400	200
Mar-18	1200	100
Apr-18	2400	200
May-18	3600	300
Jun-18	3600	300
Jul-18	1200	100
Aug-18	2400	200
Sep-18	600	50
Oct-18	600	50
Nov-18	1200	100
Dec-18	2400	200

This is not what I want, since all I want is just the total of the sales amount then divide by 12. In that table, the total is 22,800, So I want my measure to display 22,800 / 12 all the time which is 1,900

So I thought I can't use the 1st method. Then I create new measure like below :

MyMeasure per month =

VAR nTotal_Sales_LY = CALCULATE(SUMX('SalesFact', 'SalesFact'[SalesAmount]),

DATESBETWEEN('Date'[DateKey], STARTOFYEAR(SAMEPERIODLASTYEAR('Date'[DateKey])), ENDOFYEAR(SAMEPERIODLASTYEAR('Date'[DateKey])))

)

RETURN DIVIDE(nTotal_Sales_LY , 12, 0)

It has no syntax error, but when I use it, it shows error:

Something wrong with my measure ? 

What is the correct way to have fixed value ?

Thanks,

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the Date column of your Table to the Date column of your Calendar Table.  In the Calendar Table, write formulas to extract YEar and Month.  To your visual, drag Year and Month from the Calendar Table.  Try this measure

=CALCULATE([Total Sales],DATESBETWEEN(Calendar[Date],DATE(YEAR(MIN(Calendar[Date]))-1,1,1),DATE(YEAR(MIN(Calendar[Date]))-1,12,31)))/12

or

=CALCULATE([Total Sales],PREVIOUSYEAR(Calendar[Date]))/12

[OwenAuger]

@admin_xlsior 

The PARALLELPERIOD function should be useful for what you are trying to do.

PARALLELPERIOD lets you expand the date filter to complete months, quarters or years.

For example, you could write this measure to return sales for the entire current year:

Total Sales Current Year =
CALCULATE(
    [Total sales],
    PARALLELPERIOD ( 'Date'[datekey], 0, YEAR )
)

Then any of these measures are variations that return sales for the entire previous year

Total Sales LY =
CALCULATE(
    [Total sales],
    SAMEPERIODLASTYEAR ( PARALLELPERIOD ( 'Date'[datekey], 0, YEAR ) )
)

Total Sales LY =
CALCULATE(
    [Total sales],
    PARALLELPERIOD ( 'Date'[datekey], -1, YEAR )
)

Total Sales LY =
CALCULATE(
    [Total Sales Current Year],
    SAMEPERIODLASTYEAR ( 'Date'[datekey] )
)

Does that help?

Regards,

Owen