Forum Discussion

QlaroOne's avatar
QlaroOne
New Member
7 years ago
Solved

Distinct count based on multiple measures

Hi all,   I have an invoice table with following columns: - Customer - Date - Revenue   Customers can have multiple invoices per day, so multiple rows per customer and per date.   I need a r...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    QlaroOne,

    Please note that there is no relationship between date table and invoice table. You can create the following measures in the invoice table. For more details, please review attached PBIX file. 

    selectedyear = YEAR(MAX('Date'[Date]))
    selected = var selectedyearvalue=CALCULATE(SUM(invoice[Revenue]),FILTER(invoice,YEAR(invoice[Date])=[selectedyear])) return IF(ISBLANK(selectedyearvalue),0,selectedyearvalue)
    previousyear = var selectedyearvalue=CALCULATE(SUM(invoice[Revenue]),FILTER(invoice,YEAR(invoice[Date])=[selectedyear]-1)) return IF(ISBLANK(selectedyearvalue),0,selectedyearvalue)
    year-2 and before = var selectedyearvalue=CALCULATE(SUM(invoice[Revenue]),FILTER(invoice,YEAR(invoice[Date])<=[selectedyear]-2)) return IF(ISBLANK(selectedyearvalue),0,selectedyearvalue)
    new = IF(AND([selected]>0,AND([previousyear]=0,[year-2 and before]=0)),1,0) 
    existing = IF([year-2 and before]>0&&[selected]=0&&[previousyear]=0,1,0)
    recur = IF([selected]>0 &&[previousyear]>0,1,0)
    new count = SUMX(VALUES(invoice[Customer]),[new])
    existing count = SUMX(VALUES(invoice[Customer]),[existing])
    recur count = SUMX(VALUES(invoice[Customer]),[recur])



    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
7 years ago

QlaroOne,

Please note that there is no relationship between date table and invoice table. You can create the following measures in the invoice table. For more details, please review attached PBIX file. 

selectedyear = YEAR(MAX('Date'[Date]))
selected = var selectedyearvalue=CALCULATE(SUM(invoice[Revenue]),FILTER(invoice,YEAR(invoice[Date])=[selectedyear])) return IF(ISBLANK(selectedyearvalue),0,selectedyearvalue)
previousyear = var selectedyearvalue=CALCULATE(SUM(invoice[Revenue]),FILTER(invoice,YEAR(invoice[Date])=[selectedyear]-1)) return IF(ISBLANK(selectedyearvalue),0,selectedyearvalue)
year-2 and before = var selectedyearvalue=CALCULATE(SUM(invoice[Revenue]),FILTER(invoice,YEAR(invoice[Date])<=[selectedyear]-2)) return IF(ISBLANK(selectedyearvalue),0,selectedyearvalue)
new = IF(AND([selected]>0,AND([previousyear]=0,[year-2 and before]=0)),1,0) 
existing = IF([year-2 and before]>0&&[selected]=0&&[previousyear]=0,1,0)
recur = IF([selected]>0 &&[previousyear]>0,1,0)
new count = SUMX(VALUES(invoice[Customer]),[new])
existing count = SUMX(VALUES(invoice[Customer]),[existing])
recur count = SUMX(VALUES(invoice[Customer]),[recur])



Regards,
Lydia

  • QlaroOne's avatar
    QlaroOne
    New Member
    7 years ago

    Hi v-yuezhe-msft

     

    Thanks a lot for your response. Is there a way to do it with the relationship between dates en invoice table? Because I need this relationship for other measures/reports...

     

    Thanks a lot!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      QlaroOne,

      You would need to create another date table and create relationship between the date table invoice table for other measures.

      Regards,
      Lydia