Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

power bi-line chart

HI Experts,

 

greetings for the day!

 

I have two diiferent date columns one is Business date and FEMI dates. 

my logic to find the % of 1st, 2nd,3rd emi cases for november is as below

first emi due--- numarator=All FEMI cases for the month Nov22 which went into bucket 1 as of 30 Nov22.

                          Denominator= All FEMI cases for the month Nov22(count)

second emi due--- numarator=All FEMI cases for the month Oct22 which went into bucket 2 as of 30Nov22

                         ----Denominator=All FEMI cases for the month Oct22(count)

third emi due------numarator=All FEMI cases for the month Sep22 which went into bucket 3 as of 30Nov22

                      ------Denominator=All FEMI cases for the month Sep22(count)
here as of 30 Nov22 is business date and FEMI cases for the month Nov22 is FEMI date.

and here I have to show the percentages of First emi of Nov-22 and second due of Oct-22 and third due of Sep-22 as of  business date of Nov-22 on a line chart.  

when i tried I'm not getting the values on single date it is showing first due on nov second due on oct and third due on sept but all three should come on nov date.

kindly Look in to this and do the needful.

 

Thanks

Chay. 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please have a try.

    Create columns.

    date_year=year(FFEM[Dates])
    date_month=month(FFEM[Dates])

    Create a measure.

    measure=CALCULATE (
        COUNT ( FFEM[case] ),
        FILTER (
            ALL ( FFEM ),
            FFEM[year] = SELECTEDVALUE ( FFEM[year] )
                && FFEM[month] = SELECTEDVALUE ( FFEM[month] )
        )
    )
var _11=
CALCULATE (
    COUNT ( FFEM[case] ),
    FILTER (
        ALL ( FFEM ),
        FFEM[buget] = "budget 1"
            && FFEM[year] = 2022
            && FFEM[month] = 11
    )
)
var _10=
m =
CALCULATE (
    COUNT ( FFEM[case] ),
    FILTER (
        ALL ( FFEM ),
        FFEM[buget] = "budget 2"
            && FFEM[year] = 2022
            && ( FFEM[month] = 11
            || 10 )
    )
)
var _9=
m =
CALCULATE (
    COUNT ( FFEM[case] ),
    FILTER (
        ALL ( FFEM ),
        FFEM[buget] = "budget 3"
            && FFEM[year] = 2022
            && ( FFEM[month] = 11
            || 10
            || 9 )
    )
)

reutrn

IF (
    MAX ( FFEM[year] ) = 2022
        && MAX ( FFEM[month] ) = 11,
    _11 / _1number,
    IF (
        MAX ( FFEM[year] ) = 2022
            && MAX ( FFEM[month] ) = 10,
        _10 / _1number,
        IF ( MAX ( FFEM[year] ) = 2022 && MAX ( FFEM[month] ) = 9, _9 / _1number )
    )
)

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please have a try.

    Create columns.

    date_year=year(FFEM[Dates])
    date_month=month(FFEM[Dates])

    Create a measure.

    measure=CALCULATE (
        COUNT ( FFEM[case] ),
        FILTER (
            ALL ( FFEM ),
            FFEM[year] = SELECTEDVALUE ( FFEM[year] )
                && FFEM[month] = SELECTEDVALUE ( FFEM[month] )
        )
    )
var _11=
CALCULATE (
    COUNT ( FFEM[case] ),
    FILTER (
        ALL ( FFEM ),
        FFEM[buget] = "budget 1"
            && FFEM[year] = 2022
            && FFEM[month] = 11
    )
)
var _10=
m =
CALCULATE (
    COUNT ( FFEM[case] ),
    FILTER (
        ALL ( FFEM ),
        FFEM[buget] = "budget 2"
            && FFEM[year] = 2022
            && ( FFEM[month] = 11
            || 10 )
    )
)
var _9=
m =
CALCULATE (
    COUNT ( FFEM[case] ),
    FILTER (
        ALL ( FFEM ),
        FFEM[buget] = "budget 3"
            && FFEM[year] = 2022
            && ( FFEM[month] = 11
            || 10
            || 9 )
    )
)

reutrn

IF (
    MAX ( FFEM[year] ) = 2022
        && MAX ( FFEM[month] ) = 11,
    _11 / _1number,
    IF (
        MAX ( FFEM[year] ) = 2022
            && MAX ( FFEM[month] ) = 10,
        _10 / _1number,
        IF ( MAX ( FFEM[year] ) = 2022 && MAX ( FFEM[month] ) = 9, _9 / _1number )
    )
)

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

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