Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Measure Conditional

Hello,    I need to calculate in the same measure the following problem:    I have prices for 2018 and 2019, and in the same measure, I need to calculate the quatity*prices for 2018 if the date i...
Urgent Help
Mariusz's avatar
Mariusz
Community Champion
7 years ago

Hi Anonymous 

 

Can you provide a data sample?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Mariusz , 

 

I did it just right now. 

 

I just did a lot of ifs with calculates...

 

IF(sum('table'[date]<DATE[yyyy;mm;dd);

 

(IF(SUM('Tabela Categoria'[CAT_2])=1;CALCULATE(SUM('Table Sales'[Sales Price]);'Table Sales'[Year]=2018);IF(SUM('Tabela Categoria'[CAT_2])=2;CALCULATE(SUM('Table Sales'[Sales Price]);'Table Sales'[Year]=2018);IF(SUM('Tabela Categoria'[CAT_2])=3;CALCULATE(SUM('Table Sales'[Sales Price]);'VTable Sales'[Year]=2018)))));(IF(SUM('Tabela Categoria'[CAT_2])=1;CALCULATE(SUM('Table Sales'[Sales Price]);'Table Sales'[Year]=2019);IF(SUM('Tabela Categoria'[CAT_2])=2;CALCULATE(SUM('Table Sales'[Sales Price]);'Table Sales'[Year]=2019);IF(SUM('Tabela Categoria'[CAT_2])=3;CALCULATE(SUM('Table Sales'[Sales Price]);'Table Sales'[Year]=2019)))))
 
 
Do you believe you know a easier way? 
 
Thank you for your feedback.
  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    There definitely is an easier way, but it depends on how your data is modeled. Could you share a sample of the raw data with us so we can see how it is set up in your data table?  

     

    Also, if you could share a mockup of your desired visual as well, that would help too. It looks like you're trying to control and account for every possible category/year combination instead of letting your visual handle the context changes for you. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Cmcmahan 

       

      I have a table Price with the following information(In this table I have different prices by group by the prices of 2019 only be applicable after 01.10.2019.

       

      Origin|Destiny|Region|Group|Sales Price| Year | Date 

      Lisbon|Destiny|Region|A|10|2018 | Date 

      Lisbon|Destiny|Region|B|20| 2018 | Date 

      Lisbon|Destiny|Region|C|30| 2018 | Date 

      Lisbon|Destiny|Region|A|15| 2019 | 01.10.2019

      Lisbon|Destiny|Region|B|25| 2019 | 01.10.2019

      Lisbon|Destiny|Region|C|35| 2019 | 01.10.2019

       

      Table of buyer: 

       

      Origin|Destiny|Region|Group|Buyer Price| Year | Observations

      Lisbon|Destiny|Region|A|10|2018 | 

      Lisbon|Destiny|Region|B|20| 2018 |

      Lisbon|Destiny|Region|C|30| 2018 |

      Lisbon|Destiny|Region|A|15| 2019 | 

      Lisbon|Destiny|Region|B|25| 2019 | 

      Lisbon|Destiny|Region|C|35| 2019 | 

      Lisbon|Destiny|Region|A|15| 2019 | New Proposal

      Lisbon|Destiny|Region|B|25| 2019 | New Proposal

      Lisbon|Destiny|Region|C|35| 2019 | New Proposal

      Lisbon|Destiny|Region|A|15| 2019 | Applicable 

      Lisbon|Destiny|Region|B|25| 2019 | Applicable

      Lisbon|Destiny|Region|C|35| 2019 | Applicable 

       

      Table of Model Data: 

       

      Model | Group | CAT | Brand | Concatenate

      123     | 1         | A     | Apple |   123A

       

       

      Category Table

       

      CAT  | CAT_2

      A      | 1

      B      | 2

      C      | 3

       

      Table of Data

      Date| Model|Sales Value  | Buyer value

       

      Relationships

      Table of Data <--> Model Data

      Model Data <--> Category Table

      Category Table <-->Buyer Table

      Category Table <-->Sales Table

       

      I want my measure to calculate the price of 2018 for the table data until 01.10.2019,after that I want prices 2019. 

       

      thank you

       

      The data is too confusing. hope you can understand :) 

      • Cmcmahan's avatar
        Cmcmahan
        Resident Rockstar
        7 years ago

        This is very helpful.

         

        In your original post, you said that you needed to multiply the sales price with the quantity.  From the tables you shared, we can get a sales price for any given combination of Group/Date easily. How do you determine the quantity?