Need to show value based on active year month

[vengadesh_p]

Hello,

I need to achieve the following logic 

I have data like the image below

need to show Policy value based on active year & month 
Expected result 

How to achieve this, pls help me to resolve this  

Data

Policy	Start Date	End Date	No Month	Value	Value per Month
ID 1	01-03-2022	01-03-2025	36	3600	100
ID 2	01-08-2022	01-08-2024	24	1200	50

[pankajnamekar25 · Accepted solution]

You can try this measure , assuming that you have a calendar table created

Policy Value by Year =

VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])

RETURN

SUMX (

    FILTER (

        'Policy',

        YEAR('Policy'[Start Date]) <= SelectedYear &&

        YEAR('Policy'[End Date]) >= SelectedYear

    ),

    VAR StartDate = 'Policy'[Start Date]

    VAR EndDate = 'Policy'[End Date]

    VAR FromDate = DATE(SelectedYear, 1, 1)

    VAR ToDate = DATE(SelectedYear, 12, 31)

    VAR ActiveStart = MAX(StartDate, FromDate)

    VAR ActiveEnd = MIN(EndDate, ToDate)

    VAR ActiveMonths = DATEDIFF(ActiveStart, ActiveEnd, MONTH) + 1

    RETURN ActiveMonths * 'Policy'[Value per Month]

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[v-shamiliv]

Hi @vengadesh_p 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-shamiliv]

Hi @vengadesh_p 
I wanted to check if you had the opportunity to review the information provided by @sanalytics  and @pankajnamekar25 . Please feel free to contact us if you have any further questions. If their response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-shamiliv]

Hi @vengadesh_p 

Thank you for reaching out microsoft fabric community forum.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[sanalytics · Accepted solution]

@vengadesh_p 
How come your First Year is  900 only.. It should be 1000.. For ID1, if your policy start date is 1st March 2022 then within 2022 we will have 10 months ( we will have to count March as well right?). So for ID2 as well.. Let me know if logic is correct..
Now, come to your solution.. data should be transformed as far upstream as possible and as far downstream as necessary --> Always follow this statement. 
You can easily achieve your output by using the Power query rather than writing any fancy DAX.
below is the code.

let
    Source = Table,
    #"Added Custom" = Table.AddColumn(Source, "Date", each List.Dates
( [Start Date],
Number.From([End Date]) - Number.From([Start Date])+1,
#duration(1,0,0,0)
)),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month&Year", each Date.MonthName([Date]) & " " & Number.ToText( Date.Year([Date] ) )),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Policy", "Year", "Month&Year"}, {{"Value", each List.Max([Value per Month]), type nullable number}})
in
    #"Grouped Rows"

if you determined to write a DAX for that. My suggesation is to create your data first and then write the DAX. In my code remove the group by step and write below DAX.

Value = 
SUMX(
 VALUES( Source[Month&Year]),
 CALCULATE( MAX( Source[Value per Month] ))
)

Below is the screenshot and pbix file

Hope it helps

Regards

sanalytics

[pankajnamekar25 · Accepted solution]

You can try this measure , assuming that you have a calendar table created

Policy Value by Year =

VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])

RETURN

SUMX (

    FILTER (

        'Policy',

        YEAR('Policy'[Start Date]) <= SelectedYear &&

        YEAR('Policy'[End Date]) >= SelectedYear

    ),

    VAR StartDate = 'Policy'[Start Date]

    VAR EndDate = 'Policy'[End Date]

    VAR FromDate = DATE(SelectedYear, 1, 1)

    VAR ToDate = DATE(SelectedYear, 12, 31)

    VAR ActiveStart = MAX(StartDate, FromDate)

    VAR ActiveEnd = MIN(EndDate, ToDate)

    VAR ActiveMonths = DATEDIFF(ActiveStart, ActiveEnd, MONTH) + 1

    RETURN ActiveMonths * 'Policy'[Value per Month]

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.