Financial year cumulative on FY month slicer

[Anonymous]

Hi,
I am trying to get the financial year cumulative value on month selection.
On selection of particular month on slicer dropdown, FY Year month cumulative value has to get
Month slicer is sorted on Financial year wise (April to March)

All tables are related to a date field, By using date I created Fy Date, FyMonth, FyYear fields,
All Calculations are created based on FY Date.
I am achieving this cumulative by using the Fy Month range slicer.
I want to achieve this on month selection dropdown, like if I select June month -> April + may + June values

Month SLicer (Dropdown)
-------------
->April, May, June...March
---------------------------

-------------------------------------------------------------------------------
Below is the formula I am using
--------------------------------------------------------------------------------
TestMDBaseFYear = var p =SWITCH(SELECTEDVALUE(date_dim[month_name]),"August",5,"December",9,"July",4,"November",8,"October",7,"September",6,
"April",1,"February",11,"January",10,"March",12,"May",02,"June",3)
var year=SELECTEDVALUE(date_dim[FY Date].[Year])
var start1=DATE(SELECTEDVALUE(date_dim[FY Date].[Year]),"01","01")
var end1=DATE(SELECTEDVALUE(date_dim[FY Date].[Year]),p, MAX(date_dim[FY Date].[Day]))

return
// CONCATENATE(start1,end1)
IF(SELECTEDVALUE(date_dim[FY Date].[Year]) <> BLANK(),
CALCULATE(SUM(WHOLSALE_DUMP[GWP_IN_MNS_]),
//DATESBETWEEN(date_dim[FY Date].[Date],start1,end1)
FILTER(ALLEXCEPT(date_dim,date_dim[FY Date].[Year]),date_dim[Fy Date].[Date]>=start1 &&
date_dim[Fy Date].[Date]<=end1)
),0)
--------------------------------------------------------------------------------------

Please give any solution for this.

Thanks
Dileep

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous  ,

The filter in the last formula of your function should be WHOLSALE_DUMP, because you want to calculate the GWP_IN_MNS of this table

Some data I created:

Table date_dim:

Table WHOLSALE_DUMP：

Here are the steps you can follow：

1. Create measure.

Measure =
var _p =
SWITCH(
    SELECTEDVALUE(date_dim[month_name]),
    "August",5,"December",9,"July",4,"November",8,"October",7,"September",6,
    "April",1,"February",11,"January",10,"March",12,"May",02,"June",3,1)
var _year=SELECTEDVALUE('date_dim'[Year])
var _start1=DATE(_year,"01","01")
var _end1=DATE(_year,_p, MAX('date_dim'[Day]))
return
IF(_year<>BLANK(),CALCULATE(SUM(WHOLSALE_DUMP[GWP_IN_MNS]),FILTER('WHOLSALE_DUMP',YEAR([Date])=_year&&[Date]>=_start1&&[Date]<=_end1)),0
)

2. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@Anonymous , Yon can use time intelligence and date table

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/31")) // year will end on 3/31 means start date is 4/1 
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))

with month no , year

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA