how to create dynamic date columns for persons

[Anonymous]

Hi Community,

I have a data like this:

Original Data

Name	Start	End	NPriceValues
+	11/19/2017	1/31/2018	65
Aakriti Srivastava	6/12/2012	28/03/2019	43.5
Satayanaraya	10/9/2018		110
Abiodun Adebiyi	12/3/2018	04/04/2019	65

Now i want to create one table in our report with same fields and hierachy slicer.

But in table my requirement is:

Ex1: If i select FY 2018 in my slicer table will show like this

Item Description	Start	End	PriceValues
+	11/19/2017	1/31/2018	65
Aakriti Srivastava	01/04/2017	31/03/2018	43.5

Ex2:If i select FY 2019 in my slicer table will show like this

Item Description	Start	End	PriceValues
Aakriti Srivastava	01/04/2018	28/03/2019	43.5
Abiodun Adebiyi	12/3/2018	04/04/2019	65

Like this i want to show is it possible? and how to do?

Can you guys please help me out from this

Thanks in advance

Regards,

B V S S

[v-eachen-msft]

Hi, @Anonymous 

First, you need to create two columns to save your start year and end year.

Start2 =
FORMAT ( Sheet1[Start], "yyyy" )

End2 =
VAR e =
    FORMAT ( Sheet1[End], "yyyy" )
RETURN
    IF ( ISBLANK ( Sheet1[End] ), "0", e )

Then create a calendar table to get a slicer.

Create three measures to be dynamic values you need.

startDate =
VAR slicer =
    SELECTEDVALUE ( 'calendar'[FY] )
VAR e1 =
    SELECTEDVALUE ( Sheet1[End] )
VAR s1 =
    SELECTEDVALUE ( Sheet1[Start] )
VAR e2 =
    SELECTEDVALUE ( Sheet1[End2] )
VAR s2 =
    SELECTEDVALUE ( Sheet1[Start2] )
RETURN
    IF (
        slicer = e2
            && ( slicer - 1 ) = s2,
        FORMAT ( s1, "yyyy-mm-dd" ),
        IF (
            slicer < e2
                && ( slicer - 1 ) >= s2,
            (slicer-1)&"-4-1",
            IF ( slicer = e2 && ( slicer - 1 ) > s2, (slicer-1)&"-4-1" )
        )
    )

endDate =
VAR slicer =
    SELECTEDVALUE ( 'calendar'[FY] )
VAR e1 =
    SELECTEDVALUE ( Sheet1[End] )
VAR s1 =
    SELECTEDVALUE ( Sheet1[Start] )
VAR e2 =
    SELECTEDVALUE ( Sheet1[End2] )
VAR s2 =
    SELECTEDVALUE ( Sheet1[Start2] )
VAR n =
    SELECTEDVALUE ( Sheet1[Name] )
RETURN
    IF (
        slicer = e2
            && ( slicer - 1 ) >= s2,
        FORMAT ( e1, "yyyy-mm-dd" ),
        IF ( slicer < e2 && ( slicer - 1 ) >= s2, slicer&"-3-31" )

NPriceValues2 =
VAR o =
    SELECTEDVALUE ( Sheet1[NPriceValues] )
RETURN
    IF ( NOT ( ISBLANK ( Sheet1[startDate] ) ), o )

Finally, you can get your visuals.

Best Regards,

Eads

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

[Anonymous]

Hi @v-eachen-msft,

Thank you for your response,

Can you please share your pbix file.

Thank you in advance

[Anonymous]

Hi @v-eachen-msft,

Can you please have look on this and let me know about my mistake. Because i am not getting what's wrong

https://onedrive.live.com/?cid=61CA612185177FC0&id=61CA612185177FC0%21112&parId=61CA612185177FC0%211...

Please help me out from this

Thank you in advance

[Anonymous]

@v-eachen-msft ,

Previously Wrong link i pasted.

This is the correct linl :  https://1drv.ms/u/s!AsB_F4UhYcphcD_WCmPagrZz2vw

[v-eachen-msft]

Hi @Anonymous ,

Here is the pbix file link:

https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ERDOr_JjHytDg9hepi5Ub3QBeAXAaViV_8MuAf8IMY_dSg?e=8yKAKC

I changed something for the file you sent.

Best Regards,

Eads

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

[Anonymous]

hi @v-eachen-msft,

Thank you for your help.

I have downloaded your file, But i need one change..

In end date it is 03/12/FY Year (See Below) but financial year end is 03/31/ na. Please modify according to this

Thank you in advance

[Anonymous]

Hi @v-eachen-msft ,

That means if his end date is in some other financial year then it should come fy end date as his end date for selected FY Year

if he is having end date in same FY Year same end date need to show. Previously you have shown in your images

same like i want

Thank you in advance

[v-eachen-msft]

Hi @Anonymous ,

Here is the new file link.
https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ERETwRUW9WhHrgYxHb4GwfkBTuD0bU26vagRtCH8Sjibag?e=0jWzU2

Best Regards,

Eads

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

[Anonymous]

Hi @v-eachen-msft ,

Thank you for the help.

I Downloaded new copy. i think almost it's working fine. i found only one bug. 

I i select FY2020, It'sgiving like this but 1st member end date is completed na in FY2019 then also it is giving new end date. Please have a look for this also

I think this is the balance issue.

Thank you in advance

[v-eachen-msft]

Hi @Anonymous ,

Please download the new file from the following link :

https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ETdKu060UbJOtdDvUD8Vlj8BCp_s0a-A9hk_w5O7w1AtcQ?e=U8ngK0

Best Regards,

Eads

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

[Anonymous]

Hi @v-eachen-msft,

Thank you for your response. It's working fine now but i have one doubt.. In startdate and enddate measures you gave harcoded dates & years but if we have thousands of records in our data we can't give hardcoded dates like this.. Is it correct?? Can you please explain me little more or please modify in our pbix file.

IF

(s1>mind&&s1<maxd&&NOT(ISBLANK(e1))&&NOT(specialyear="2020"),s1,IF(s1>=maxdp&&s1<=mind&&NOT(ISBLANK(e1))&&NOT(specialyear="2020"),s1,IF(s1<maxdp&&e1>mind&&NOT(ISBLANK(e1))&&NOT(specialyear="2020"),mind,IF(specialyear="2020"&&e1=DATE("2019","4","4"),DATE("2019","4","1"),IF(s1=DATE("2012","6","12")&&specialyear="2013",DATE("2013","3","31")))))

)

Thank you in advance

[Anonymous]

Hi

Any Suggestions??