How to create a measure for Current FY -Current Month data in power BI

[Anonymous]

Hi,

i have current FY actual data (Achvievement) and currently we are there in Sep month 7th date, i need write a measure for Current FY  Achvievement - Current month(Sep), So here data should come til August, Can some one support here

[Ashish_Mathur]

Hi,

Create a Calendar Table with a relationship (Many to One and Single) between the Date column of your Fact table to the Date column of the Calendar Table.  Write these measures

Total = sum9Data[Achievement])

Total YTD till previous month = calculate([Total],datesbetween(calendar[date],date(year(today()),1,1),eomonth(today(),-1)))

This measure assumes the FY to start from Jan.

Hope this helps.

[rajendraongole1 · Accepted solution]

Hi @Anonymous -create a measure as below that checks the month of the Dated column, filters out the current month, and sums the Achievement values for the other months. (Dont create a calculated column).

Use below measure:

CurrentFY_Achievement_ExcludingCurrentMonth =
VAR CurrentMonth = MONTH(TODAY()) -- Get the current month number
VAR CurrentYear = YEAR(TODAY()) -- Get the current year
RETURN
CALCULATE(
SUM('Table'[Achievement]),
'Table'[Month_No] < CurrentMonth, -- Exclude the current month
'Table'[Years] = CurrentYear -- Filter for the current fiscal year
)

Hope this helps.