Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
looking to calculate day diff, but in a specific month. so in the example below, a new column to show days in Jan2012, first record should show 6 and others 0.
basically looking for a visual to show the total number of DAYSdiff in a specific month, there might be an easier way than above.
any help is appreciated!
Solved! Go to Solution.
hi, @Anonymous
If you try to improve the formula as below:
Measure 2 = IF ( SELECTEDVALUE ( 'Table'[DATE1] ) <= MAX ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[DATE1] ) >= MIN ( 'Date'[Date] ), DATEDIFF ( SELECTEDVALUE ( 'Table'[DATE1] ), MAX ( 'Date'[Date] ) + 1, DAY ), IF ( SELECTEDVALUE ( 'Table'[DATE1] ) <= MIN ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[DATE2] ) >= MAX ( 'Date'[Date] ), DATEDIFF ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) + 1, DAY ), IF ( SELECTEDVALUE ( 'Table'[DATE1] ) <= MIN ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[DATE2] ) <= MAX ( 'Date'[Date] )&& SELECTEDVALUE ( 'Table'[DATE2] )>=MIN('Date'[Date]), DATEDIFF ( MIN ( 'Date'[Date] ), SELECTEDVALUE ( 'Table'[DATE2] ) + 1, DAY ), IF ( SELECTEDVALUE ( 'Table'[DATE1] ) >= MIN ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[DATE2] ) <= MAX ( 'Date'[Date] ), DATEDIFF ( SELECTEDVALUE ( 'Table'[DATE1] ), MAX ( 'Date'[Date] ) + 1, DAY ), 0 ) ) ) )
Best Regards,
Lin
hi, @Anonymous
For your case, you could try this way as below:
Step1:
Create a separate date table for select specific month.
Date = ADDCOLUMNS ( CALENDARAUTO (), "YearMonth", YEAR ( [Date] ) * 100 + MONTH ( [Date] ) )
Step2:
Use this logic to create a measure as below:
DAYSdiff = IF ( SELECTEDVALUE ( 'Table'[DATE1] ) <= MAX ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[DATE1] ) <= MIN ( 'Date'[Date] ), DATEDIFF ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) + 1, DAY ), IF ( SELECTEDVALUE ( 'Table'[DATE1] ) <= MAX ( 'Date'[Date] ), DATEDIFF ( SELECTEDVALUE ( 'Table'[DATE1] ), MAX ( 'Date'[Date] ) + 1, DAY ), 0 ) )
Result:
here is my sample pbix, please try it.
Best Regards,
Lin
Although it is informative, it is not the solution.
in the scenario below, only record 3 should have 30 days and all other record should be 0.
hi, @Anonymous
If you try to improve the formula as below:
Measure 2 = IF ( SELECTEDVALUE ( 'Table'[DATE1] ) <= MAX ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[DATE1] ) >= MIN ( 'Date'[Date] ), DATEDIFF ( SELECTEDVALUE ( 'Table'[DATE1] ), MAX ( 'Date'[Date] ) + 1, DAY ), IF ( SELECTEDVALUE ( 'Table'[DATE1] ) <= MIN ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[DATE2] ) >= MAX ( 'Date'[Date] ), DATEDIFF ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) + 1, DAY ), IF ( SELECTEDVALUE ( 'Table'[DATE1] ) <= MIN ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[DATE2] ) <= MAX ( 'Date'[Date] )&& SELECTEDVALUE ( 'Table'[DATE2] )>=MIN('Date'[Date]), DATEDIFF ( MIN ( 'Date'[Date] ), SELECTEDVALUE ( 'Table'[DATE2] ) + 1, DAY ), IF ( SELECTEDVALUE ( 'Table'[DATE1] ) >= MIN ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[DATE2] ) <= MAX ( 'Date'[Date] ), DATEDIFF ( SELECTEDVALUE ( 'Table'[DATE1] ), MAX ( 'Date'[Date] ) + 1, DAY ), 0 ) ) ) )
Best Regards,
Lin
Created as column
Diff from date1 = VAR seldate = DATE(2013,05,31) Return if(Sales[Order_Date] < seldate, DATEDIFF(Sales[Order_Date],seldate,DAY),0)
In case you want a measure dependent on time/calendar
Diff from date3 = VAR seldate = max('OrderTime'[Order Date]) Return CALCULATE(sumx(Sales, if(Sales[Order_Date] < seldate, DATEDIFF(Sales[Order_Date],seldate,DAY),0)))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |