How to show YTD and LYTD on line chart without using date table

[itsmebvk]

Hi Folks,

I have a requirement, I tried all possible ways but failed to get desired output.

I am trying to create YTD and LYTD calculation based on year i slected in slicer. The problem is I dont have any continuous dates and I dont want to create a cusom date table too. I wan to show these YTD and LYTD on Line visual, and these YTD and LYTD values should change automatically based on Year I selected in slicer.

Can we create YTD and LYTD using Fiscal Period and Fiscal Month ( I have it in data set) columns instead of just date?

Thanks in advance for your inputs.

[Anonymous]

Hi @itsmebvk,

Yes, it is possible. You can extract current date value from current row content and use date functions to processing and defined the filter range in the DAX formula conditions.

CYTD Measure=
VAR currDate =
    MAX ( table[Date] )
RETURN
    CALCULATE (
        SUM ( table[amount] ),
        FILTER (
            ALLSELECTED ( table ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && [Date] <= currDate
        )
    )

LYTD Measure=
VAR currDate =
    MAX ( table[Date] )
RETURN
    CALCULATE (
        SUM ( table[amount] ),
        FILTER (
            ALLSELECTED ( table ),
            YEAR ( [Date] ) = YEAR ( currDate )-1
                && [Date] <= currDate
        )
    )

If you still confused about on coding formula. Please provide some dummy data and expected results with raw table structure to help us clarify your requirement and do test coding formula on it.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@itsmebvamsi , Test as

YTD ?
var _max á maxx(allselected(Table),Table[Date])
var _min - date(year(_min),1,1) //Change this based on you FY

devolución
calculate(sum(Tbale[value]),filter(all(Table[Date]), Table[Date] >-_min && Table[Fecha] <-_max))

LYTD ?
var _max1 á maxx(allselected(Table),Table[Date])
var _max á date(year(_max1)-1,month(_max1),day(_max1))
var _min - date(year(_min),1,1) //Change this based on you FY

devolución
calculate(sum(Tbale[value]),filter(all(Table[Date]), Table[Date] >-_min && Table[Fecha] <-_max))

But it's better to have a date chart. That's the suggested way. I'm not sure why you want to stop that.

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

Calendar

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

[lbendlin]

Not sure why you want to torture yourself like that, but yes, that is possible.

Have two measures that calculate if a data value is in their respective year, and return blank if not.  Add both measures to the value well.  Then create a calculated table with the x axis values (Fiscal period, Fiscal Month) with the year part stripped away.