Fiscal Year Sales Measures

[st-dat]

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]))

[v-chuncz-msft]

@st-dat,

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

[st-dat]

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.

Date	Name	Payment
03/05/2017	Company-A	5.00
03/11/2017	Company-B	5.00
03/20/2017	Company-C	5.00
03/08/2017	Company-D	5.00
04/11/2017	Company-E	10.00
04/20/2017	Company-F	20.00
04/08/2017	Company-G	20.00
04/09/2017	Company-H	20.00
04/10/2017	Company-C	10.00
04/11/2017	Company-A	20.00
04/12/2017	Company-B	20.00
05/13/2017	Company-C	5.00
05/14/2017	Company-A	10.00
05/15/2017	Company-B	10.00
05/16/2017	Company-C	50.00
05/17/2017	Company-A	10.00
		225.00

[st-dat]

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