Forum Discussion

Matteoss's avatar
Matteoss
Regular Visitor
4 years ago

Revenues variable

Hi there, 

 

I have a model with three fact tables "Importi_2020" "Importi_2021" and "Importi_2022" all linked to a Calendar table. 

I need to implement a "Revenue Variable" that changes its value according to the year. 

I cannot merge the tables because they have different relationships with other dimension tables.

The output should allow me to have on the same timeline the value of Revenue from 2020 to 2022 using just the Revenue Variable..

I know it is not so difficult but I get in trouble using IF clause with Year of the calendar table 

 

Appreciate your help, 

Matteo 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Matteoss , You should append (union) if possible. else create this year ve last year measure on two different table 

     

    example

     

    YTD Sales = CALCULATE(SUM(Importi_2021[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

     

     

     

    other example measures are

     

    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

    Last YTD Sales = CALCULATE(SUM(Importi_2020[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

     

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA

  • Matteoss's avatar
    Matteoss
    Regular Visitor
    4 years ago

    Thanks for your fast reply, 

     

    I try to explain i little better what is my issue. 

    It is like i have three sales tables one for each year (2020, 2021, 2022). I cannot append them since they have been implemented differently. What is the same is the final value of total sales for each month. 

    I think the solution could be related to a variable that give me as output the correct sales amount linked to the three tables according to the year. 

    I do not think the measures you give me can work in my case. 

    Sound it reasonable for you? 

     

    • v-zhangti's avatar
      v-zhangti
      Community Support
      4 years ago

      Hi, Matteoss 

       

      Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

       

      Best Regards,

      Community Support Team _Charlotte

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