YTD,PY and PYTD Amounts for selected fiscal year and period slicers

[Srilekha]

I have fiscal year and period slicers, based on selected fiscal year , period, need to get start and end dates of the period and get previous fiscal year amounts and need to calculate YTD and previous year YTD based on selected fiscal year slicer by ignoring period slicer values while calculating YTD

I have written below measure to get Amount for selected year and period, Howerver, facing issues with YTD and PYTD and selected previous fiscal year amounts

Actual =

var startd=CALCULATE(min(dimDate[PeriodStart]),FILTER(dimDate,dimDate[PostingFiscalYear]=[Selected FY]),FILTER(dimDate,dimDate[PostingPeriod]=[SelectedPeriod]))

var endd=CALCULATE(max(dimDate[PeriodEnd]),FILTER(dimDate,dimDate[PostingFiscalYear]=[Selected FY]),FILTER(dimDate,dimDate[PostingPeriod]=[SelectedPeriod]))

var result = CALCULATE(SUM(factGLEntryTrailer[Amount]),factGLEntryTrailer[Created Date]>=startd,factGLEntryTrailer[Created Date]<=endd)

return result

Below are the measures which I have tried but not workign as expected. please help me

YTD Actual =

var fy=SELECTEDVALUE(dimDate[PostingFiscalYear])

var startd=CALCULATE(min(dimDate[PeriodStart]),ALL(dimDate[PostingPeriod]),dimDate[PostingFiscalYear]=fy)

--var endd=CALCULATE(max(dimDate[PeriodEnd]),dimDate[Date]=TODAY())

var result = CALCULATE(SUM(factGLEntryTrailer[Amount]),FILTER(ALLSELECTED(factGLEntryTrailer),factGLEntryTrailer[Created Date]>=startd &&factGLEntryTrailer[Created Date]<=TODAY()),GROUPBY('dw vw_gl_derive','dw vw_gl_derive'[rptfmt_description]))

return result

PY Actual1 =

var x = [Selected FY]-1

var y = [SelectedPeriod]

var startd=CALCULATE(min(dimDate[PeriodStart]),FILTER(ALL(dimDate[PostingFiscalYear]),dimDate[PostingFiscalYear]=x),FILTER(ALL(dimDate[PostingPeriod]),dimDate[PostingPeriod]=y))

var endd=CALCULATE(max(dimDate[PeriodEnd]),FILTER(ALL(dimDate[PostingFiscalYear]),dimDate[PostingFiscalYear]=x),FILTER(ALL(dimDate[PostingPeriod]),dimDate[PostingPeriod]=y))

var result = CALCULATE(SUM(factGLEntryTrailer[Amount]),FILTER(ALL(factGLEntryTrailer),factGLEntryTrailer[Created Date]>=startd || factGLEntryTrailer[Created Date]<=endd),GROUPBY('dw vw_gl_derive','dw vw_gl_derive'[rptfmt_description]))

return result

PYTD Actual1 =

var startd=CALCULATE(min(dimDate[PeriodStart]),FILTER(dimDate,dimDate[PostingFiscalYear]=[SelectedPreviousFY]))

var endd=CALCULATE(max(dimDate[Date]),year(dimDate[Date])=year(SELECTEDVALUE(dimDate[Date]))-1,MONTH(dimDate[Date])=MONTH(SELECTEDVALUE(dimDate[Date])),DAY(dimDate[Date])=DAY(SELECTEDVALUE(dimDate[Date])))

var result = CALCULATE(SUM(factGLEntryTrailer[Amount]),factGLEntryTrailer[Created Date]>=startd,factGLEntryTrailer[Created Date]<=TODAY())

return result

[amitchandak]

@Srilekha , if you have date with help from date table you can try measure like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

if you want based on today

YTD Today=
var _min = date(year(today()),1,1)
var _day = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

LYTD =
var _min = date(year(today())-1,1,1)
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s