Forum Discussion

st-dat's avatar
st-dat
Icon for Helper III rankHelper III
7 years ago

Fiscal Year Sales Measures

 

Hello,

 

I need help to write MEASURES for below, so that I can reuse elsewhere ( I have calendar table and

Fiscal year start April 1 and Ends March 31. I'm using PowerPivot excel 2016.

  • Previous YTD : = ?
  • Previous Fiscal Quarter := ?
  • Month Todate := ?
  • Previous Month Todate := ?
  • Week Todate := ?
  • Previous Week Todate := ?

 

 

>>>>>>I have used below queries and they work well for my need:

>>Fiscal  YTD :=IF([Net Sales]>0,CALCULATE([Net Sales],DATESYTD(dCalendar[Date],"03/31")))

>>Fiscal Quarter :=IF([Net Sales]>0,CALCULATE([Net Sales],DATESQTD(dCalendar[Date])))

 

 

I have tried to use the queries below and some other but all failed to work or displayed blank result:

 

******Error in syntax and did not processed

WTD:=IF (
HASONEVALUE ( DimDate[Year] )
&& HASONEVALUE (DimDate[Working Week] ),
CALCULATE (
SUM (Orders[Unique_Recipients] ),
FILTER (
ALL ( DimDate ),
DimDate[Year] = VALUES ( DimDate[Year] )
&& DimDate[Working Week] = VALUES (DimDate[Working Week])
&& DimDate[Date Value] <= MAX ( DimDate[Date Value])
)
),
BLANK ()
)

 

  • ****Failed and displayed value for the previous year instead of previous Month

 Previous Month := CALCULATE([Net Sales], PREVIOUSMONTH(dCalendar[Date]))

 

Need Help

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    st-dat,

     

    It would be better to share us a simplified example. You may try to apply virtual relationship in DAX.

    • st-dat's avatar
      st-dat
      Icon for Helper III rankHelper III
      7 years ago

      Hi Sam,

       

      Thank you for your response, Kindly use the sample data set below and let me know if you required additional information. Best regards.

       

      DateNamePayment
      03/05/2017Company-A5.00
      03/11/2017Company-B5.00
      03/20/2017Company-C5.00
      03/08/2017Company-D5.00
      04/11/2017Company-E10.00
      04/20/2017Company-F20.00
      04/08/2017Company-G20.00
      04/09/2017Company-H20.00
      04/10/2017Company-C10.00
      04/11/2017Company-A20.00
      04/12/2017Company-B20.00
      05/13/2017Company-C5.00
      05/14/2017Company-A10.00
      05/15/2017Company-B10.00
      05/16/2017Company-C50.00
      05/17/2017Company-A10.00
        225.00
      • st-dat's avatar
        st-dat
        Icon for Helper III rankHelper III
        7 years ago

        Hi,

         

        Furthermore, I was able get below three to work as expected but unfortunately the rest could not.

         

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

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

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

         

        When I modified the above queries to achieve the following: (1)Prev Year, (2)Month, (3)Prev Month, (4)Week, and (5) Prev Week. The result was 'blanks' or high value far above expected value or an error message like "

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

         

        Thank you for your help.

         

        St-dat