Forum Discussion

Isgandar_B's avatar
Isgandar_B
Frequent Visitor
4 years ago

Power BI YoY different tables

Hi dear community,

I have a question related to YoY.

So, I have 2 tables, for example one with CY 2022 by month (Jan'22, Feb'22 and etc.) and another one CY 2021 (Jan'21, Feb'21 and etc.) they have same structure.

For now i solve my problem just putted them in one excel file, and using CALCULATE & DATEADD combination.

But are we have any solution not put them together? I want keep years separetely in different file, but keep option manupulate with data like those data in 1 file.

How I can do this?

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Isgandar_B , Both tables can join with  a common date table and and you can get current year from one and last year from second

     

    example

     

    current  = SUM(Sales[Sales Amount])

     

    past

     

    Year behind Sales = CALCULATE(SUM(Sales1[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Year behind Sales = CALCULATE(SUM(Sales1[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

    • Isgandar_B's avatar
      Isgandar_B
      Frequent Visitor
      4 years ago

      Hi amitchandak, 
      Thanks for quick reply, but as i understand so i need another table with dates like calendar to can link them over date, because in one file month only from 2021 and in another one it is only 2022

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Isgandar_B ,

    Here are the steps you can follow：

    1. Create measure.

    Divide =
var _1=
CALCULATE(SUM('Table2'[Sales - Net+VAT]),FILTER(ALL(Table2),'Table2'[Month]=MAX('Table1'[Month])&&'Table2'[Year]=MAX('Table1'[Year])-1))
return
DIVIDE(
    MAX('Table1'[Sales - Net+VAT]),_1)

    2. Result:

     

    Best Regards,

    Liu Yang

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

    • Isgandar_B's avatar
      Isgandar_B
      Frequent Visitor
      4 years ago

      Hi Liu Yang, thanks for you reply

      I can't test your solution, because I'm just new one in using Power BI, and don't understand DAX as professionals, so i can't just copied you answer because for first i need to understand it step by step, in general i understand it, but FILTER side looks very complicated for me actually, so i can't figure out with this (

  • Isgandar_B's avatar
    Isgandar_B
    Frequent Visitor
    4 years ago

    Let me share some screenshots, so first table is data for 2022, and second one 2021.
    IYA - AZN in first table is just YoY formula (as i say previosly for now i solve this problem just with merging 2 tables in 1) Its get May'22 and divide it for May'21 results.

    My formula - 

    IYA - AZN = DIVIDE(SUM(RAW[Sales - Net+VAT]) , CALCULATE(SUM(RAW[Sales - Net+VAT]),DATEADD(RAW[Billing date].[Date],-12,MONTH)))



    So i need same but just keeping 2021 and 2022 in sepated files.