Forum Discussion
Days computation
Hi,
So.. I am stuck again. 😥
I have this data:
MATERIALOFFLOADING DATELOADING DATE
| MAT1 | 05/01/2021 | |
| MAT1 | 20/03/2021 | |
| MAT2 | 05/01/2021 | |
| MAT2 | 01/04/2021 | |
| MAT3 | 28/03/2021 |
For the Output,
I am tying to achieve these:
Any help is appreciated.
Thank you!
6 Replies
Hoping someone can help me on this 😢
Hi mussaenda ,
Could you pls provide the formula for the number of days?
Mat1 28 day in Janaury， how to use the following to calculate it?
MATERIALOFFLOADING DATELOADING DATE
MAT1 05/01/2021 MAT1 20/03/2021 Best Regards
Lucien
Sorry v-luwang-msft ,
That should be 26 days. (The Feb must be 28.)
Days between 05/01/2021 and 31/01/2021 = 26 for month of January. (Or I think it should be 27?)
Days between 01/02/2021 and 28/02/2021 = 28 for month of February.
Hi mussaenda ，
You could try the following steps，let first output:
You could use the following measures:
end = VAR end1 = CALCULATE ( MAX ( 'Table'[LOADING DATE] ), FILTER ( ALL ( 'Table' ), 'Table'[MATERIAL] = MAX ( 'Table'[MATERIAL] ) ) ) VAR end2 = IF ( end1 <> BLANK (), end1, TODAY () ) VAR test1 = DATEDIFF ( MAX ( 'Table'[OFFLOADING DATE] ), end2, DAY ) RETURN test1Then the secend output：
You could try the following steps:
Step1, create start_dt and end_dt,base on base table :
start_dt = FORMAT(MAX('Table'[OFFLOADING DATE]),"YYYY/MM/DD")end_DATE1 = VAR end1 = CALCULATE ( MAX ( 'Table'[LOADING DATE] ), FILTER ( ALL ( 'Table' ), 'Table'[MATERIAL] = MAX ( 'Table'[MATERIAL] ) ) ) VAR end2 = IF ( end1 <> BLANK (), end1, TODAY () ) RETURN end2then get just as following:
And then use the following measures:
January：
onemon = VAR test1 = IF ( 'Table'[start_dt] >= FORMAT ( "2021/01/01", "YYYY/MM/DD" ) && 'Table'[start_dt] <= FORMAT ( "2021/01/31", "YYYY/MM/DD" ) && 'Table'[start_dt] <> BLANK (), IF ( FORMAT ( [end_DATE1], "YYYY/MM/DD" ) <= FORMAT ( "2021/01/31", "YYYY/MM/DD" ), DATEDIFF ( MAX ( 'Table'[OFFLOADING DATE] ), [end_DATE1], DAY ) + 1, DATEDIFF ( MAX ( 'Table'[OFFLOADING DATE] ), "2021/1/31", DAY ) + 1 ), IF ( 'Table'[start_dt] <= FORMAT ( "2021/01/01", "YYYY/MM/DD" ) && 'Table'[start_dt] <> BLANK (), IF ( FORMAT ( [end_DATE1], "YYYY/MM/DD" ) <= FORMAT ( "2021/01/31", "YYYY/MM/DD" ), DATEDIFF ( "2021/01/01", [end_DATE1], DAY ) + 1, DATEDIFF ( "2021/01/01", "2021/1/31", DAY ) + 1 ), 0 ) ) RETURN test1Jan = IF(HASONEVALUE('Table'[MATERIAL]),[onemon], SUMX(ALL('Table'),'Table'[onemon]))February：
twomon = VAR test1 = IF ( 'Table'[start_dt] >= FORMAT ( "2021/02/01", "YYYY/MM/DD" ) && 'Table'[start_dt] <= FORMAT ( "2021/02/28", "YYYY/MM/DD" ) && 'Table'[start_dt] <> BLANK (), IF ( FORMAT ( [end_DATE1], "YYYY/MM/DD" ) <= FORMAT ( "2021/02/28", "YYYY/MM/DD" ), DATEDIFF ( MAX ( 'Table'[OFFLOADING DATE] ), [end_DATE1], DAY ) + 1, DATEDIFF ( MAX ( 'Table'[OFFLOADING DATE] ), "2021/02/28", DAY ) + 1 ), IF ( 'Table'[start_dt] <= FORMAT ( "2021/02/01", "YYYY/MM/DD" ) && 'Table'[start_dt] <> BLANK (), IF ( FORMAT ( [end_DATE1], "YYYY/MM/DD" ) <= FORMAT ( "2021/02/28", "YYYY/MM/DD" ), DATEDIFF ( "2021/02/01", [end_DATE1], DAY ) + 1, DATEDIFF ( "2021/02/01", "2021/02/28", DAY ) + 1 ), 0 ) ) RETURN test1Feb = IF(HASONEVALUE('Table'[MATERIAL]),[twomon], SUMX(ALL('Table'),'Table'[twomon]))March ：
threemon = VAR test1 = IF ( 'Table'[start_dt]>= FORMAT("2021/03/01","YYYY/MM/DD") &&'Table'[start_dt]<= FORMAT("2021/03/31","YYYY/MM/DD") && 'Table'[start_dt] <> BLANK (), IF ( FORMAT([end_DATE1],"YYYY/MM/DD") <=FORMAT("2021/03/31","YYYY/MM/DD"), DATEDIFF ( MAX ( 'Table'[OFFLOADING DATE] ), [end_DATE1], DAY )+1, DATEDIFF ( MAX ( 'Table'[OFFLOADING DATE] ), "2021/03/31", DAY )+1 ), IF('Table'[start_dt]<=FORMAT("2021/03/01","YYYY/MM/DD") && 'Table'[start_dt] <> BLANK (),IF ( FORMAT([end_DATE1],"YYYY/MM/DD") <=FORMAT("2021/03/31","YYYY/MM/DD"), DATEDIFF ("2021/03/01", [end_DATE1], DAY )+1, DATEDIFF ( "2021/03/01", "2021/03/31", DAY )+1 ),0) ) RETURN test1Mar = IF(HASONEVALUE('Table'[MATERIAL]),[threemon], SUMX(ALL('Table'),'Table'[threemon]))April ：
formonth = VAR test1 = IF ( 'Table'[start_dt]>= FORMAT("2021/04/01","YYYY/MM/DD") &&'Table'[start_dt]<= FORMAT("2021/04/30","YYYY/MM/DD") && 'Table'[start_dt] <> BLANK (), IF ( FORMAT([end_DATE1],"YYYY/MM/DD") <=FORMAT("2021/04/30","YYYY/MM/DD"), DATEDIFF ( MAX ( 'Table'[OFFLOADING DATE] ), [end_DATE1], DAY )+1, DATEDIFF ( MAX ( 'Table'[OFFLOADING DATE] ), "2021/04/30", DAY )+1 ), IF('Table'[start_dt]<=FORMAT("2021/04/01","YYYY/MM/DD") && 'Table'[start_dt] <> BLANK (),IF ( FORMAT([end_DATE1],"YYYY/MM/DD") <=FORMAT("2021/04/30","YYYY/MM/DD"),if(FORMAT([end_DATE1],"YYYY/MM/DD") <FORMAT("2021/04/01","YYYY/MM/DD"),0, DATEDIFF ("2021/04/01", [end_DATE1], DAY )+1), DATEDIFF ( "2021/04/01", "2021/04/30", DAY )+1 ), 0) ) RETURN test1Apr = IF(HASONEVALUE('Table'[MATERIAL]),[formonth], SUMX(ALL('Table'),'Table'[formonth]))Click follow to download pbix file if you need!
Wish it is helpful for you!
Best Regards
Lucien
Hi v-luwang-msft ,
Thank you so much for your solution!
But..
Your solution is static. Sorry I did not elaborate.
Because we all have the month of the year, and for the next year..
Not only for these three particular months.
I provided these months as sample data.
But thank you so much!
For now, I have achieved my requirement by doing the date series/range for each material.
It will not be a good query for a long run because it will load lots of data.
Hi mussaenda ,
If your problem has been solved, you could accept yourself reply as the solution to help others find it.Best RegardsLucien
