Solved: Time intelligence functions result not as expected

Anonymous · ‎01-04-2021

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

v-yiruan-msft · ‎01-07-2021

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-yiruan-msft · ‎01-07-2021

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak · ‎01-04-2021

@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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Anonymous · ‎01-05-2021

* Sale - capture to - date sale

Sale Rep	Date	Item code name	Sell-out (qty)	Shipped (qty)
TR China & Taiwan	6/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		1172
TR China & Taiwan	7/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	70	690
TR China & Taiwan	8/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	409
TR China & Taiwan	9/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	876	805
TR China & Taiwan	10/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	200	994
TR China & Taiwan	11/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	575	385
TR China & Taiwan	12/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		139
TR HK & Macau	6/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		352
TR HK & Macau	7/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	1
TR HK & Macau	8/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	58
TR HK & Macau	9/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	137	66
TR HK & Macau	10/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	78	100
TR HK & Macau	11/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	97	19
TR HK & Macau	12/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		90
TR Japan	6/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		63
TR Japan	8/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		12
TR Japan	10/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	1
TR Japan	11/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	3
TR Korea	6/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		262
TR Korea	7/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		1500
TR Korea	8/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	599
TR Korea	9/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	6	432
TR Korea	10/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	111	30
TR Korea	11/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	1	6
TR Korea	12/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		90
TR Oceania	6/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		5
TR SEA	6/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		570
TR SEA	9/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		765
TR SEA	10/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		600
TR SEA	11/1/2020	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL		70
TR China & Taiwan	6/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		9316
TR China & Taiwan	7/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	2639	2684
TR China & Taiwan	8/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	5359	5080
TR China & Taiwan	9/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	8819	3981
TR China & Taiwan	10/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	1438	4660
TR China & Taiwan	11/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	2946	2428
TR China & Taiwan	12/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		4900
TR HK & Macau	6/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		5162
TR HK & Macau	7/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	139	380
TR HK & Macau	8/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	2290	660
TR HK & Macau	9/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	2167	475
TR HK & Macau	10/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	1634	2680
TR HK & Macau	11/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	1089	1593
TR HK & Macau	12/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		1719
TR Japan	6/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		76
TR Japan	9/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		27
TR Japan	10/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	11
TR Japan	11/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	14	3
TR Japan	12/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		39
TR Korea	6/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		4500
TR Korea	7/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		8320
TR Korea	8/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	6617
TR Korea	9/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	15508	10655
TR Korea	10/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	15414	17333
TR Korea	11/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	13083	13439
TR Korea	12/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		14691
TR Oceania	6/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		38
TR Oceania	8/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		3
TR Oceania	9/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		161
TR Oceania	10/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	44	20
TR Oceania	11/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	59
TR Oceania	12/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		23
TR SEA	6/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		1266
TR SEA	8/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		970
TR SEA	9/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		1590
TR SEA	10/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	44	2577
TR SEA	11/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL	59	1125
TR SEA	12/1/2020	G061555 - AB/ROY 20 LOT 150ML BTL		2400
TR China & Taiwan	6/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		2793
TR China & Taiwan	7/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL	382	520
TR China & Taiwan	8/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL	2642
TR China & Taiwan	9/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL	108	4571
TR China & Taiwan	10/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL	2200	2884
TR China & Taiwan	11/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL	2215	1022
TR China & Taiwan	12/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		6217
TR HK & Macau	10/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL	157	222
TR HK & Macau	11/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL	62	723
TR HK & Macau	12/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		1114
TR Japan	11/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		18
TR Japan	12/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		6
TR Korea	10/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL	223	703
TR Korea	11/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL	697	924
TR Korea	12/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		1392
TR Oceania	9/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		10
TR Oceania	10/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		20
TR Oceania	12/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		7
TR SEA	6/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		1799
TR SEA	7/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		216
TR SEA	9/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		3247
TR SEA	10/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		1834
TR SEA	11/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL	4	472
TR SEA	12/1/2020	G061589 - AB/ROY 20 LOT 300ML BTL		2804

Anonymous · ‎01-05-2021

* Outcome

	*Sale to Oct	*Sale to Dec
Row Labels	Sell-out (qty) Launch Period	Shipped (qty) Launch period	Sell-out (qty) to date	Shipped (qty) to date
G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	2546	8418	3222	9217
G061555 - AB/ROY 20 LOT 150ML BTL	62123	82614	79373	124974
G061589 - AB/ROY 20 LOT 300ML BTL	5712	18819	8690	33518
Total	70381	109851	91285	167709

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 · ‎01-05-2021

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

Campaign	OCD	Market	Item	Fcst Qty
S300	8/1/2020 0:00	TR Asia	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	125
S300	8/1/2020 0:00	TR Australia	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	87
S300	8/1/2020 0:00	TR DFS Asia	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	1831
S300	8/1/2020 0:00	TR Greater China	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	3021
S300	8/1/2020 0:00	TR Japan	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	415
S300	8/1/2020 0:00	TR Korea	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	2025
S300	8/1/2020 0:00	TR South East	G061454 - AB/ROY 20 CLEAN/OIL 150ML PUMP BTL	190
S300	8/1/2020 0:00	TR Asia	G061555 - AB/ROY 20 LOT 150ML BTL	157
S300	8/1/2020 0:00	TR Australia	G061555 - AB/ROY 20 LOT 150ML BTL	407
S300	8/1/2020 0:00	TR DFS Asia	G061555 - AB/ROY 20 LOT 150ML BTL	6241
S300	8/1/2020 0:00	TR Greater China	G061555 - AB/ROY 20 LOT 150ML BTL	21021
S300	8/1/2020 0:00	TR Japan	G061555 - AB/ROY 20 LOT 150ML BTL	865
S300	8/1/2020 0:00	TR Korea	G061555 - AB/ROY 20 LOT 150ML BTL	23025
S300	8/1/2020 0:00	TR South East	G061555 - AB/ROY 20 LOT 150ML BTL	840
S300	8/1/2020 0:00	TR Greater China	G061589 - AB/ROY 20 LOT 300ML BTL	11050
S300	8/1/2020 0:00	TR Korea	G061589 - AB/ROY 20 LOT 300ML BTL	1640

Time intelligence functions result not as expected

Helpful resources

Join us at the Microsoft Fabric Community Conference

Join our Community Sticker Challenge 2025

Power BI Monthly Update - January 2025

Fabric Community Update - January 2025

How to Get Your Question Answered Quickly

New Offer! Become a Certified Fabric Data Engineer