Half YoY difference on half Year (Whole Number) field, Not Date field

[dakshayaniswami]

Hi,

I'm relatively new to DAX and have a challenge.

I have a file with a Year column (no month, no day in any other column). I can't change the year to a date or all years turn to 1905. I want to do a semiannual YoY % change calculation. I've tried multiple versions of measures and no luck. Here's a sample of the data:

Company	vendor	Half Year	Revenue
Quark	Quark	2018H1	$173.01
Quark	Quark	2018H2	$34.33
Quark	Quark	2019H1	$59.48
Adobe	Adobe	2018H1	$149.17
Adobe	Adobe	2018H2	$72.93
Adobe	Adobe	2019H1	$17.32

Any guidance is appreciated.

Thank you !

Dakshayani

[AllisonKennedy · Accepted solution]

@dakshayaniswami You'll need to somehow convert that Year column to a date. You can do this in Power Query using Split column > Delimiter > Custom > H

Then add a column > Conditional column > IF Year.2 = 1 then 1/1/ else 7/1 

(Or use the start month for each half year).

Now combine Year.1 with the new Custom column using the "Merge Columns" button (use Ctrl to select both columns). 

Then you'll have a date column > change type to date. Connect to a date table and you'll be able to use CALCULATE and DATEADD to get your YoY growth. Excel with Allison: DimDate: What, Why and How? 

[v-cgao-msft · Accepted solution]

Hi @dakshayaniswami ,

Please try this measure:

Measure = 
VAR _cur_hy = SELECTEDVALUE('Table'[Half Year])
VAR _next_hy = CALCULATE(MIN('Table'[Half Year]),'Table'[Half Year]>_cur_hy)
VAR _next_hy_revenue = CALCULATE([Total revenue],'Table'[Half Year]=_next_hy)
VAR _rate = IF(ISBLANK(_next_hy_revenue),BLANK(),DIVIDE(_next_hy_revenue-[Total revenue],[Total revenue]))
RETURN
_rate

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[dakshayaniswami]

Hello Gao,

I need help in this same problem.

My first question is the dax function you gave is for semiannual growth means between eg. 2018H1 & 2019H1

Or for sequential growth between e.g 2018H1 & 2018H2  ??

Can you please provide dax function for both.

Thank you in advance

[v-cgao-msft]

Hi @dakshayaniswami ,

You can split [Half Year] into [Year] and [Period].

then try this measure.

Measure = 
VAR _cur_year = SELECTEDVALUE('Table'[Year])
VAR _last_year = _cur_year - 1
VAR _period = SELECTEDVALUE('Table'[Period])
VAR _cur_revenue = SUM('Table'[Revenue])
VAR _same_period_last_year_revenue = 
CALCULATE(
    SUM('Table'[Revenue]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Year]=_last_year
                &&'Table'[Period]=_period
        )
)
RETURN
IF(
    ISBLANK(_same_period_last_year_revenue),
    BLANK(),
    DIVIDE(
        SUM('Table'[Revenue])-_same_period_last_year_revenue,
        _same_period_last_year_revenue
    )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[dakshayaniswami]

Hello @v-cgao-msft 

The solution you gave its right but its not working with muy custmized filters.

I am attaching my file. Can you Please check  https://github.com/daksh0444/My-work/blob/13e7df1cb39b311a3b85d9f5fab97995e06a05f1/Dash1%20-%20Copy%... 

Spoiler

Thank you

[dakshayaniswami]

Hello Gao,

Wonderful. I just wanted to drop a quick note and say thank you so much for your help. It's working perfectly for me and I really appreciate your assistance. Thank you so much, it was a great help!

Best,

Dakshaayni Swami
dakshayani

[AllisonKennedy · Accepted solution]

@dakshayaniswami You'll need to somehow convert that Year column to a date. You can do this in Power Query using Split column > Delimiter > Custom > H

Then add a column > Conditional column > IF Year.2 = 1 then 1/1/ else 7/1 

(Or use the start month for each half year).

Now combine Year.1 with the new Custom column using the "Merge Columns" button (use Ctrl to select both columns). 

Then you'll have a date column > change type to date. Connect to a date table and you'll be able to use CALCULATE and DATEADD to get your YoY growth. Excel with Allison: DimDate: What, Why and How?