Forum Discussion

menphis21's avatar
menphis21
Icon for Helper IV rankHelper IV
3 years ago

Year over year without blank

Hi,

 

I am trying to calculate a simple Year over Year, but when i am using the Prior Year amount, it show me in the table all the month of the year blank.

The probleme is its take in account all the previous period amount instead of taking the selected period which is until july 2024

 

 

YearCal Month NameBI PartnerCurrent periodPrevious periodCollection Mom amountCollection Amount YoY%
2023JulyX1436764114821425%25.13%
2023AugustX9658001163823-17%-17.01%
2023SeptemberX2141258564432279%279.37%
2023OctoberX105022278728033%33.40%
2023NovemberX9159061314162-30%-30.30%
2023DecemberX2276381191486819%18.88%
2023JanuaryX2526859178405142%41.64%
2023FebruaryX1902772111647970%70.43%
2023MarchX93031183285812%11.70%
2023AprilX1765239774473128%127.93%
2023MayX1979149553429258%257.62%
2023JuneX271169125637896%5.77%
2024JulyX1734835143676421%20.75%
2024AugustX 965800-100% 
2024SeptemberX 2141258-100% 
2024OctoberX 1050222-100% 
2024NovemberX 915906-100% 
2024DecemberX 2276381-100% 
2024JanuaryX 2526859-100% 
2024FebruaryX 1902772-100% 
2024MarchX 930311-100% 
2024AprilX 1765239-100% 
2024MayX 1979149-100% 
2024JuneX 2711691-100% 

 

I tried something like that but the total is still not correct :

Collection Amount YoY% =

VAR CollectionAmountN1 =
    if( ISBLANK(Collection[Collection Amount]),0,CALCULATE(
        [Collection Amount],
        DATEADD(Collection[Fiscal date].[Date], -12, MONTH)
    ))
Return
 If ( ISBLANK(Collection[Collection Amount]),0, (DIVIDE ( Collection[Collection Amount] , CollectionAmountN1 ) - 1 ))
 
 
need you help please
Need Help

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi menphis21 - create a measure to calculate the collection amount using sameperiodlastyear function 

     

    Previous Year Amount =
    CALCULATE(
    [Collection Amount],
    SAMEPERIODLASTYEAR(Collection[Fiscal date].[Date])
    )

     

    create another measure YOY

    YoY Percentage =
    VAR CurrentYearAmount = [Collection Amount]
    VAR PreviousYearAmount = [Previous Year Amount]
    RETURN
    IF(
    NOT ISBLANK(CurrentYearAmount) && NOT ISBLANK(PreviousYearAmount),
    DIVIDE(CurrentYearAmount - PreviousYearAmount, PreviousYearAmount, 0),
    BLANK()
    )

     

    Hope your visual filters out blank values appropriately. This step depends on how you set up your visual and filters in Power BI.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!