Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
i would like to calculate a volume for Year (starting 01/04/2024) to max date of a date table for example (21/04/2024).
i have a date table, and a data table with volumes.
Thank youy
Solved! Go to Solution.
Hi @NewbieJono
You may try these measures for Current YTD and Last Year YTD in the same date range.
YTD =
VAR maxDate = CALCULATE ( MAX ( 'DateTable'[Date] ), ALL ( 'DateTable' ) )
VAR curYear = YEAR ( maxDate )
VAR startDate = DATE ( IF ( MONTH ( maxDate ) < 4, curYear - 1, curYear ), 4, 1 )
RETURN
CALCULATE (
SUM ( 'Table'[volume] ),
ALL ( 'DateTable'[Date] ),
'DateTable'[Date] >= startDate,
'DateTable'[Date] <= maxDate
)
LY YTD =
VAR maxDate = EDATE ( CALCULATE ( MAX ( 'DateTable'[Date] ), ALL ( 'DateTable' ) ), -12 )
VAR curYear = YEAR ( maxDate )
VAR startDate = DATE ( IF ( MONTH ( maxDate ) < 4, curYear - 1, curYear ), 4, 1 )
RETURN
CALCULATE (
SUM ( 'Table'[volume] ),
ALL ( 'DateTable'[Date] ),
'DateTable'[Date] >= startDate,
'DateTable'[Date] <= maxDate
)
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
@NewbieJono You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Otherwise, provide sample data. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Sorry i am very new to dax.
i just want to add my total form 01 April to date in one metric. and another metric to show the total of the same date range.
i tried same period last year but it seems to be returning the whole months volumes
Hi @NewbieJono
You may try these measures for Current YTD and Last Year YTD in the same date range.
YTD =
VAR maxDate = CALCULATE ( MAX ( 'DateTable'[Date] ), ALL ( 'DateTable' ) )
VAR curYear = YEAR ( maxDate )
VAR startDate = DATE ( IF ( MONTH ( maxDate ) < 4, curYear - 1, curYear ), 4, 1 )
RETURN
CALCULATE (
SUM ( 'Table'[volume] ),
ALL ( 'DateTable'[Date] ),
'DateTable'[Date] >= startDate,
'DateTable'[Date] <= maxDate
)
LY YTD =
VAR maxDate = EDATE ( CALCULATE ( MAX ( 'DateTable'[Date] ), ALL ( 'DateTable' ) ), -12 )
VAR curYear = YEAR ( maxDate )
VAR startDate = DATE ( IF ( MONTH ( maxDate ) < 4, curYear - 1, curYear ), 4, 1 )
RETURN
CALCULATE (
SUM ( 'Table'[volume] ),
ALL ( 'DateTable'[Date] ),
'DateTable'[Date] >= startDate,
'DateTable'[Date] <= maxDate
)
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Hi,
Share data in a format that can be pasted in an MS Excel file. For that dummy dataset, show the expected result.
User | Count |
---|---|
98 | |
90 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |