Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
7 years ago

Create fixed measure

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 salesx
Jan-181200100
Feb-182400200
Mar-181200100
Apr-182400200
May-183600300
Jun-183600300
Jul-181200100
Aug-182400200
Sep-1860050
Oct-1860050
Nov-181200100
Dec-182400200

 

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,

 

 

 

 

 

 

 

Need Help

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    7 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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