Forum Discussion

Isnaan_Ahmed's avatar
Isnaan_Ahmed
Frequent Visitor
3 years ago

Help with Time Intelligence using Fiscal Years

Hi all!

 

Hope you can help with an issue I'm having - I am trying to create a table that lists the ordered units by Fiscal Year (this part works) with another column showing ordered units in the previous fiscal year. 

 

I have a fiscal dates table set up like this:

 So using the below DAX I assumed I would be able to create a table visual that shows ordered units for each FY and the previous FY also, however it is coming up blank each time:

_OrderedUnits Last FY =
VAR _SelectedFY = SELECTEDVALUE ( _FiscalDates[Fiscal Year (544)]  )
VAR _MinDate =
EDATE(
    CALCULATE (
        FIRSTDATE ( _FiscalDates[Date] ) ,
        _FiscalDates[Fiscal Year (544)] = _SelectedFY
    ),
    -12)
VAR _MaxDate =
EDATE(
    CALCULATE (
        LASTDATE ( _FiscalDates[Date] ) ,
        _FiscalDates[Fiscal Year (544)] = _SelectedFY
    ),
    -12)
VAR _Return =
CALCULATE (
    [01. Ordered Units],
    _FiscalDates[Date] >= _MinDate && _FiscalDates[Date] <= _MaxDate
)

Return
_Return
 
The table _FiscalDates has a single relationship to the Dim table using the date column. The visual currently looks like this:

 

Any help would be much appreciated!

 

1 Reply

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Isnaan_Ahmed 

    try like:

    _OrderedUnits Last FY =
VAR _SelectedFY = SELECTEDVALUE ( _FiscalDates[Fiscal Year (544)]  )
RETURN
    CALCULATE(
        [01. Ordered Units],
        _FiscalDates[Fiscal Year (544)] = LEFT(_SelectedFY, 2)&(RIGHT(_SelectedFY,2)-1)
)