getting fiscal YTD based on filtered year and month

[Easley08]

Hi,

currently im getting the whole fiscal year from July 2022 - June 2023 

but my filter month is till May 2023 only how to get the YTD of July 2022 - May 2023 only

expected output : 43M only 

current measure: 

PNL AU Year to Date:=
var selectedYear = year(max(FLOATING_DATE[Date]))
var selectedMonth = MONTH(max(FLOATING_DATE[Date]))
var selectedYearMonth = FORMAT(max(FLOATING_DATE[Date]),"YYYYMM")

return

CALCULATE([PNL AMOUNT],FILTER(DIM_DATE,DIM_Date[Fiscalyear]=selectedYear),ALL(SELECT_PERIOD))

ive try to add DIM_DATE[fiscalmonth] <= selectedMonth but its not getting the same value

FLOATING_DATE TABLE

DIM_DATE TABLE

thanks.

[v-zhangti · Accepted solution]

Hi, @Easley08 

You can try the following methods.
Date:

Start date of the financial year = IF(MONTH([Date])<7,DATE(Year([Date])-1,7,1),DATE(Year([Date]),7,1))

Measure = Var _Start=CALCULATE(MAX('Date'[Start date of the financial year]),FILTER(ALL('Date'),[Date]=SELECTEDVALUE('Date'[Date])))
Return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Date]>=_Start&&[Date]<=SELECTEDVALUE('Date'[Date])))

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...