Measure for calculating cumulative sales per Fiscal period

[st-dat]

Hello 

Could someone help correct my mistakes in the below measures or suggest another better option, they are not working for me and/or with syntax error. I’m using PowerPivot excel 2016 and Fiscal Year (start date 1st April – Ends March 31st), and need a measure to return just a value. I hope to re-use the measure by modification in other calculations.

Many thanks.

Week To Date Sales :=

CALCULATE (

    [Sales],

    FILTER (

        ALL ( DATE ),

        'Date'[YearWeekNumber] = MAX ( 'Date'[YearWeekNumber] )

            && 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )

Previous Week To Date Sales :=

CALCULATE (

    [Sales],

    FILTER (

        ALL ( DATE ),

        'Date'[YearWeekNumber] = MAX ( 'Date'[YearWeekNumber] ) – 1

            && 'Date'[Date] <= MAX ( 'Date'[PM Date] ) )  )

Month To date:=

SUMX (VALUES ( 'Date'[YearMonthNumber] ),IF (CALCULATE ( COUNTROWS ( VALUES ( 'Date'[Date] ) ) ) = CALCULATE ( VALUES ( 'Date'[MonthDays] ) ),CALCULATE ([Sales], ALL ( 'Date' ),FILTER(     ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) - )),CALCULATE ([Sales],ALL ( 'Date' ),CALCULATETABLE ( VALUES ( 'Date'[MonthDayNumber] ) ),FILTER (ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber]= EARLIER ( 'Date'[YearMonthNumber] ))))

Previous Month:=

SUMX (VALUES ( 'Date'[YearMonthNumber] ),IF (CALCULATE ( COUNTROWS ( VALUES ( 'Date'[Date] ) ) ) = CALCULATE ( VALUES ( 'Date'[MonthDays] ) ),CALCULATE ([Sales], ALL ( 'Date' ),FILTER(     ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) - 1)),CALCULATE ([Sales],ALL ( 'Date' ),CALCULATETABLE ( VALUES ( 'Date'[MonthDayNumber] ) ),FILTER (ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber]= EARLIER ( 'Date'[YearMonthNumber] ) - 1)))

Previous Quarter:=

SUMX (VALUES ( 'Date'[YearMonthNumber] ),IF (CALCULATE ( COUNTROWS ( VALUES ( 'Date'[Date] ) ) ) = CALCULATE ( VALUES ( 'Date'[MonthDays] ) ),CALCULATE ([Sales], ALL ( 'Date' ),FILTER(     ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) - 3)),CALCULATE ([Sales],ALL ( 'Date' ),CALCULATETABLE ( VALUES ( 'Date'[MonthDayNumber] ) ),FILTER (ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber]= EARLIER ( 'Date'[YearMonthNumber] ) - 3)))

Previous Year :=

SUMX (VALUES ( 'Date'[YearMonthNumber] ),IF (CALCULATE ( COUNTROWS ( VALUES ( 'Date'[Date] ) ) ) = CALCULATE ( VALUES ( 'Date'[MonthDays] ) ),CALCULATE ([Sales], ALL ( 'Date' ),FILTER(     ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) - 12)),CALCULATE ([Sales],ALL ( 'Date' ),CALCULATETABLE ( VALUES ( 'Date'[MonthDayNumber] ) ),FILTER (ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber]= EARLIER ( 'Date'[YearMonthNumber] ) - 12)))

[Ashish_Mathur]

Hi,

If there is relationship from the Date column of the base data table to the Date column of the Date Table, then try try this.  FIrst drag Year/Month/Quarer from the Calendar Table and then:

1. Select a certain month and write this measure to compute sales in the previous month = CALCULATE([Sales],PREVIOUSMONTH(Date[Date]))
2. Select a certain quarter and write this measure to compute sales in the previous month = CALCULATE([Sales],PREVIOUSQUARTER(Date[Date]))
3. Select a certain year and write this measure to compute sales in the previous month = CALCULATE([Sales],PREVIOUSYEAR(Date[Date]))

[st-dat]

Hi Ashish,

Thank you for your kindness, the 3 dax actually work as described. I tried to use it in a way to suit the requirement I'm working on but the month displays values for few future dates.

I'm thinking a single value measure would be most suitable for my purpose.

I'm currently using the following and they are working perfectly...may be this could provide idea of my issue and solution.

SalesFiscalYear:=IF([TotalSales]>0,CALCULATE([TotalSales],DATESYTD(dCalendar[Date],"03/31")))

SalesFiscalQuarter:=IF([Totalsales]>0,CALCULATE([TotalSales],DATESQTD(dCalendar[Date])))

SalesPrevQuarter:=CALCULATE([TotalSales],DATEADD(dCalendar[Date],-1,QUARTER))

I have also modified them for other periods but the result was either 'blank', far over expected value or error message like "

Semantic error: A column specified in the call to function DATEADD is not type of Date. This is not supported."

[Ashish_Mathur]

Hi,

I just do not understand your question.  Someone else will help you.

[st-dat]

Hi Ashish,

That's ok, Your effort so far is highly appreciated.

The exact solution you have provided is the solution I wanted in a measure form that returns a single value for week and previous week sales. Having it in calculated column is not suitable for the actual purpose.

Thank you

St-dat

[st-dat]

Hi,

Thank you so much for your kind response, there exist a relationship between date columns of the base table and date table. Your suggestion is well understood and would like to clarify an espect where you mentioned that "FIrst drag Year/Month/Quarter from the Calendar Table and then:" 'Select a certain month - quarter, and - year'. Could you please simplify this term for a rookie like me using PowerPivot excel 2016 platform.

That being said, I would also appreciate correction on the measures I posted or possible suggestions on those not yet resolved such as Week, previous week, and previous month. 

Best regards

[Ashish_Mathur]

Hi,

Your measures for previous year, period and quarter are unduly complicated.  Just drag month to the visual and drag in the measure for previous month which i have written.  Now drag out the month and drag in the quarter and also meaure for previous quarter which i have written.

[st-dat]

That's ok, I will work on it as suggested, thank you so much

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...