Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Time intelligence functions result not as expected

 

Hi expert,

Im working with time intelligent function, but the result was not as what i expected it to be. Could you please help? 

I have Sell-out qty (Launch period) = On counter date - 4 months to On counter date + 2 months

Launch period Sell-out =
VAR _max= MAXX('Date',DATEADD('FAST'[International OCD],2,MONTH))
VAR _min= MINX('Date',DATEADD('FAST'[International OCD],-4,MONTH))
Return
CALCULATE([(Fcst)OCD to date Sel-in],DATESBETWEEN('Date'[Date],_min,_max))
 
I also have Sell-out qty (to date) = OCD - 4 mths to current date
OCD to date Sell-out = CALCULATE(sum('Sell-out NewBiz'[Sell-out (qty)]),USERELATIONSHIP('Sell-out NewBiz'[Item code],FAST[Item (code - name)]))
 

OCD is Aug

Sell out (launch period) = Apr'20 to Oct'20 = 62,123

Sell out (to date) = Apr'20 to Jan'21 = 79,373

 

and here is the result 

Sell-out (launch period) = Sell-out to date

Could you please advise if my DAX are wrong? 

thank you very much 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    Please try to the formula of measure and check whether it can get the correct result. If not, please check whether you created any relationship between Date and your fact table(for example: FAST). And please provide its formula if [(Fcst)OCD to date Sel-in] is a measure. Thank you.

     

    Launch period Sell-out =
CALCULATE (
    [(Fcst)OCD to date Sel-in],
    DATESBETWEEN (
        'Date'[Date],
        FIRSTDATE ( DATEADD ( 'Date'[Date], -4, MONTH ) ),
        LASTDATE ( DATEADD ( 'Date'[Date], 2, MONTH ) )
    )
)

     

    In addition, you can refer the content in the following links and hope they can help you.

    Previous Dynamic Period using DAX in Power BI

    DAX Time Intelligence for Power BI

    Best Regards

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    Please try to the formula of measure and check whether it can get the correct result. If not, please check whether you created any relationship between Date and your fact table(for example: FAST). And please provide its formula if [(Fcst)OCD to date Sel-in] is a measure. Thank you.

     

    Launch period Sell-out =
CALCULATE (
    [(Fcst)OCD to date Sel-in],
    DATESBETWEEN (
        'Date'[Date],
        FIRSTDATE ( DATEADD ( 'Date'[Date], -4, MONTH ) ),
        LASTDATE ( DATEADD ( 'Date'[Date], 2, MONTH ) )
    )
)

     

    In addition, you can refer the content in the following links and hope they can help you.

    Previous Dynamic Period using DAX in Power BI

    DAX Time Intelligence for Power BI

    Best Regards

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Please use date table, date add need continuous date, any missing date it will not work.

     

    Check my video reason it can fail :https://www.youtube.com/watch?v=OBf0rjpp5Hw&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=3

     

    You can try like

    Rolling 6 = CALCULATE([(Fcst)OCD to date Sel-in],DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-3)+1,6,MONTH))

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Chandak:

      Thank you for your advice, however i could not achive the outcome i wanted. 

      There are few table i working on it, that why i used the "userrelationship" as i mentioned above. 

      Here is summarize 2 tables: 

      * Launch - captured OCD

      CampaignOCDMarket ItemFcst Qty
      S3008/1/2020 0:00TR AsiaG061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL125
      S3008/1/2020 0:00TR AustraliaG061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL87
      S3008/1/2020 0:00TR DFS AsiaG061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL1831
      S3008/1/2020 0:00TR Greater ChinaG061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL3021
      S3008/1/2020 0:00TR JapanG061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL415
      S3008/1/2020 0:00TR KoreaG061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL2025
      S3008/1/2020 0:00TR South EastG061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL190
      S3008/1/2020 0:00TR AsiaG061555 - AB/ROY 20 LOT 150ML BTL157
      S3008/1/2020 0:00TR AustraliaG061555 - AB/ROY 20 LOT 150ML BTL407
      S3008/1/2020 0:00TR DFS AsiaG061555 - AB/ROY 20 LOT 150ML BTL6241
      S3008/1/2020 0:00TR Greater ChinaG061555 - AB/ROY 20 LOT 150ML BTL21021
      S3008/1/2020 0:00TR JapanG061555 - AB/ROY 20 LOT 150ML BTL865
      S3008/1/2020 0:00TR KoreaG061555 - AB/ROY 20 LOT 150ML BTL23025
      S3008/1/2020 0:00TR South EastG061555 - AB/ROY 20 LOT 150ML BTL840
      S3008/1/2020 0:00TR Greater ChinaG061589 - AB/ROY 20 LOT 300ML BTL11050
      S3008/1/2020 0:00TR KoreaG061589 - AB/ROY 20 LOT 300ML BTL1640



    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      * Outcome 

       *Sale to Oct*Sale to Dec
      Row LabelsSell-out (qty) Launch PeriodShipped (qty) Launch period Sell-out (qty) to dateShipped (qty) to date
      G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL2546841832229217
      G061555 - AB/ROY 20 LOT 150ML BTL621238261479373124974
      G061589 - AB/ROY 20 LOT 300ML BTL571218819869033518
      Total7038110985191285167709

       

      Appologize for multiple replies, as error msg appeared when i trying to post 3 tables toghether

      Looking forward for your solution. 

      Thank you so much 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      * Sale - capture to - date sale

      Sale RepDateItem code nameSell-out (qty)Shipped (qty)
      TR China & Taiwan6/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 1172
      TR China & Taiwan7/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL70690
      TR China & Taiwan8/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL409 
      TR China & Taiwan9/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL876805
      TR China & Taiwan10/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL200994
      TR China & Taiwan11/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL575385
      TR China & Taiwan12/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 139
      TR HK & Macau6/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 352
      TR HK & Macau7/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL1 
      TR HK & Macau8/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL58 
      TR HK & Macau9/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL13766
      TR HK & Macau10/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL78100
      TR HK & Macau11/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL9719
      TR HK & Macau12/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 90
      TR Japan6/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 63
      TR Japan8/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 12
      TR Japan10/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL1 
      TR Japan11/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL3 
      TR Korea6/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 262
      TR Korea7/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 1500
      TR Korea8/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL599 
      TR Korea9/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL6432
      TR Korea10/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL11130
      TR Korea11/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL16
      TR Korea12/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 90
      TR Oceania6/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 5
      TR SEA6/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 570
      TR SEA9/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 765
      TR SEA10/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 600
      TR SEA11/1/2020G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL 70
      TR China & Taiwan6/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 9316
      TR China & Taiwan7/1/2020G061555 - AB/ROY 20 LOT 150ML BTL26392684
      TR China & Taiwan8/1/2020G061555 - AB/ROY 20 LOT 150ML BTL53595080
      TR China & Taiwan9/1/2020G061555 - AB/ROY 20 LOT 150ML BTL88193981
      TR China & Taiwan10/1/2020G061555 - AB/ROY 20 LOT 150ML BTL14384660
      TR China & Taiwan11/1/2020G061555 - AB/ROY 20 LOT 150ML BTL29462428
      TR China & Taiwan12/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 4900
      TR HK & Macau6/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 5162
      TR HK & Macau7/1/2020G061555 - AB/ROY 20 LOT 150ML BTL139380
      TR HK & Macau8/1/2020G061555 - AB/ROY 20 LOT 150ML BTL2290660
      TR HK & Macau9/1/2020G061555 - AB/ROY 20 LOT 150ML BTL2167475
      TR HK & Macau10/1/2020G061555 - AB/ROY 20 LOT 150ML BTL16342680
      TR HK & Macau11/1/2020G061555 - AB/ROY 20 LOT 150ML BTL10891593
      TR HK & Macau12/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 1719
      TR Japan6/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 76
      TR Japan9/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 27
      TR Japan10/1/2020G061555 - AB/ROY 20 LOT 150ML BTL11 
      TR Japan11/1/2020G061555 - AB/ROY 20 LOT 150ML BTL143
      TR Japan12/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 39
      TR Korea6/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 4500
      TR Korea7/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 8320
      TR Korea8/1/2020G061555 - AB/ROY 20 LOT 150ML BTL6617 
      TR Korea9/1/2020G061555 - AB/ROY 20 LOT 150ML BTL1550810655
      TR Korea10/1/2020G061555 - AB/ROY 20 LOT 150ML BTL1541417333
      TR Korea11/1/2020G061555 - AB/ROY 20 LOT 150ML BTL1308313439
      TR Korea12/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 14691
      TR Oceania6/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 38
      TR Oceania8/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 3
      TR Oceania9/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 161
      TR Oceania10/1/2020G061555 - AB/ROY 20 LOT 150ML BTL4420
      TR Oceania11/1/2020G061555 - AB/ROY 20 LOT 150ML BTL59 
      TR Oceania12/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 23
      TR SEA6/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 1266
      TR SEA8/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 970
      TR SEA9/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 1590
      TR SEA10/1/2020G061555 - AB/ROY 20 LOT 150ML BTL442577
      TR SEA11/1/2020G061555 - AB/ROY 20 LOT 150ML BTL591125
      TR SEA12/1/2020G061555 - AB/ROY 20 LOT 150ML BTL 2400
      TR China & Taiwan6/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 2793
      TR China & Taiwan7/1/2020G061589 - AB/ROY 20 LOT 300ML BTL382520
      TR China & Taiwan8/1/2020G061589 - AB/ROY 20 LOT 300ML BTL2642 
      TR China & Taiwan9/1/2020G061589 - AB/ROY 20 LOT 300ML BTL1084571
      TR China & Taiwan10/1/2020G061589 - AB/ROY 20 LOT 300ML BTL22002884
      TR China & Taiwan11/1/2020G061589 - AB/ROY 20 LOT 300ML BTL22151022
      TR China & Taiwan12/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 6217
      TR HK & Macau10/1/2020G061589 - AB/ROY 20 LOT 300ML BTL157222
      TR HK & Macau11/1/2020G061589 - AB/ROY 20 LOT 300ML BTL62723
      TR HK & Macau12/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 1114
      TR Japan11/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 18
      TR Japan12/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 6
      TR Korea10/1/2020G061589 - AB/ROY 20 LOT 300ML BTL223703
      TR Korea11/1/2020G061589 - AB/ROY 20 LOT 300ML BTL697924
      TR Korea12/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 1392
      TR Oceania9/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 10
      TR Oceania10/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 20
      TR Oceania12/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 7
      TR SEA6/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 1799
      TR SEA7/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 216
      TR SEA9/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 3247
      TR SEA10/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 1834
      TR SEA11/1/2020G061589 - AB/ROY 20 LOT 300ML BTL4472
      TR SEA12/1/2020G061589 - AB/ROY 20 LOT 300ML BTL 2804