topic Re: getting fiscal YTD based on filtered year and month in DAX Commands and Tips

getting fiscal YTD based on filtered year and month

Easley08 — Thu, 14 Dec 2023 05:47:12 GMT

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.

Re: getting fiscal YTD based on filtered year and month

lbendlin — Mon, 18 Dec 2023 00:50:50 GMT

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-Forum/ba-p/963216

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/1447523

Re: getting fiscal YTD based on filtered year and month

v-zhangti — Thu, 28 Dec 2023 09:57:26 GMT

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.