Forum Discussion

romeo95's avatar
romeo95
Frequent Visitor
8 years ago

compare sales offices

hi, I write you to ask you a help. I must compare the sales of the offices for year, but I have a problem when an office doesn't result to have sales in one year. I have a field id, year and office...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

romeo95,

Please check if the following DAX return your expected result, if not, please describe more about your desired result.


previousyearsales = CALCULATE(FIRSTNONBLANK(TAB1[sales],1),FILTER(TAB1,TAB1[id]=EARLIER(TAB1[id])&&TAB1[year]=EARLIER(TAB1[year])-1))
diff = IF(ISBLANK(TAB1[sales])||ISBLANK(TAB1[previousyearsales]),BLANK(),TAB1[sales]-TAB1[previousyearsales])



Regards,
Lydia

romeo95's avatar
romeo95
Frequent Visitor
8 years ago

thank you, I'll try it soon as possible

  • romeo95's avatar
    romeo95
    Frequent Visitor
    8 years ago

    Hi,

    it doesn't work

    The problem is this

    id    sales                
    201701/office1    100                
    201702/office1    55                
    201801/office1    75                
                        
                        
                                    2017                   2018    
                                 year    year-1    year    year-1
                   month                
    office1        1          100                   75    100
                      2            55            
                      
            total              155                   75    100

    There aren't sales for 201802 and the total year-1 of 2018 doesn't calculate it

    Now I try to insert the no match record with 0 as sales

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      romeo95,

      You add Month field this time. Please share your PBIX file with dummy data to me so that I can provide you exact DAX.

      You can upload PBIX file to OneDrive and post shared link of the PBIX file.

      Regards,
      Lydia