Fiscal YTD and compare Fiscal Period to date

Question

I am having an issue with writing the measures to accomplish my requirements.

I am using a [slicerMeasure] to determine the Fiscal Year(FY) and another [slicerMeasure2] to determine Last Fiscal Year(FY-1).

I need to pull back the sum of all orders from (FY) to current Month and Day for (FY)

and compare sum of All orders from  (FY-1) to current Month and Day for (FY-1) .

I have Dim_Dates[FiscalYear_Begin] and Dim_Dates[FiscalYear_End] in a seperate table.

Current measures are:

slicerMeasure:=IF(COUNTROWS(FY) = 1 ,VALUES((FY[Fiscal Year])), BLANK())
slicerMeasure2:=IF(COUNTROWS(FY) = 1 ,(VALUES(FY[Fiscal Year]))-1, BLANK())
Total:=SUMX(Counts,Counts[ORDERED_QUANTITY])

If today() = 01-23-2016...
Fiscal_Year_Begin_Date (2016) = 6/28/2015 - from the Dim_Dates[Fiscal_Year_Begin_Date] Table/Column
Fiscal_Year_Begin_Date (2015) = 6/29/2014 - from the Dim_Dates[Fiscal_Year_Begin_Date] Table/Column

I want ot pull back
[Total] for 6/28/2015 to 1/23/2016
and compare
[Total] for 6/29/2014 to 1/23/2015

 Any assistance would be appreciated.

Answer 1

There are standard DAX functions for this - the only thing is it seems your fiscal years begin on different days (2015: 6/28, 2016: 6/29)? If your fiscal years are consistent, you can use TOTALYTD to compute the year-to-date total:

ytd_total:=TOTALYTD([Total],Dim_Dates[Date],,"6/27")

The fourth argument to TOTALYTD is the last day of the year, which enables working with broken years.

To compute the same for the previous year, filter the ytd measure with SAMEPERIODLASTYEAR(Dim_Dates[Date]):

ytd_previousyear:=CALCULATE([ytd_total], SAMEPERIODLASTYEAR(Dim_Dates[Date]))

When you want to force a ytd total until TODAY(), no matter what period is selected, I'd filter [ytd_total] to today:

ytd_today:=CALCULATE([ytd_total],Dim_Dates[Date]=TODAY())

Answer 2

Assuming a date table that contains a list of all dates, a fiscal year for each date, and a fiscal day of year for each date:

Orders:=
SUM( FactOrder[OrderAmount] )

OrdersYTD:=
CALCULATE(
    [Orders]
    ,FILTER(
        ALL( DimDate )
        ,DimDate[FiscalYear] = MAX( DimDate[FiscalYear] )
            && DimDate[FiscalDayOfYear] <= MAX( DimDate[FiscalDayOfYear] )
    )
)

OrdersYTD Prior:=
CALCULATE(
    [Orders]
    ,FILTER(
        ALL( DimDate )
        ,DimDate[FiscalYear] = MAX( DimDate[FiscalYear] ) - 1
            && DimDate[FiscalDayOfYear] <= MAX( DimDate[FiscalDayOfYear] )
    )
)

These will work with any fiscal calendar, so long as you have those three fields defined, and much more flexibly than the built-in time intelligence functions.

---

Check out the Power BI User Group of Philadelphia.
Our next meeting is February 2 in Malvern, PA.