Forum Discussion

aJ2's avatar
aJ2
Helper I
7 years ago
Solved

Using comparison operators with 'Date' data type

Hi.

 

I have created two measures for a common purpose ie i want to calculate the conversions before July 2018 from one table A and from July 2018 from another table B. When I want to split it between different months, it creates an aggregate rather than just for the particular month.

 

The formula for table A:- conversions_old = CALCULATE(SUM(tableA[conversions]), "MONTH('date'[date_key])<7, YEAR('date'[date_key])<=2018)

 

The formula for table B:- conversions_new = CALCULATE(DISTINCTCOUNT(tableB[conversions]), all_txns[platform]="app", all_txns[order_id]<282349, MONTH('date'[date_key])>6, YEAR('date'[date_key])>=2018)

 

The combined measure is conversions_all = conversions_new + conversions_old.

 

So when I split the same by year_month, it gives an aggregated value rather than splitting it by month.

 

Kindly help me resolve this issue.

 

 

Need Help
Show and Tell
Tips and Tricks
  • aJ2's avatar
    aJ2
    7 years ago

    Hi rajulshah

     

    I was able to filter according to the dates by using the FILTER() function.

     

    The formulas are- 

     

    conversions app new = CALCULATE(SUM(tableA[conversions]), FILTER('date', FORMAT('date'[date_key], "yyyy-mm") > "2018-06"))

     

    conversions app new = CALCULATE(SUM(tableB[conversions]), FILTER('date', FORMAT('date'[date_key], "yyyy-mm") < "2018-07"))

     

     

    Thank you for your assisstance.

     

3 Replies

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    7 years ago

    Hello aJ2,

     

    Can you please share your current data vs your expected data?

    • aJ2's avatar
      aJ2
      Helper I
      7 years ago

      Hi rajulshah

       

      I was able to filter according to the dates by using the FILTER() function.

       

      The formulas are- 

       

      conversions app new = CALCULATE(SUM(tableA[conversions]), FILTER('date', FORMAT('date'[date_key], "yyyy-mm") > "2018-06"))

       

      conversions app new = CALCULATE(SUM(tableB[conversions]), FILTER('date', FORMAT('date'[date_key], "yyyy-mm") < "2018-07"))

       

       

      Thank you for your assisstance.

       

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi aJ2,

         

        Have you solved your problem?

         

        If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

         

        If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

         

        Best Regards,

        Cherry