Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
This is the given table.
Is the below output possible in DAX?
Thanks in advance.
Solved! Go to Solution.
Hi, @John_Dozen34999 ,
According to your description, you could create table to output 12 months and use GENERATE() to connect 2 tables,
then create a column of calculated values, and finally put it in matrix and sort by index. the following formula to create :
Step1: Enter data to create a table and add index column:
Step2: New table
Gnew_table = GENERATE(SUMMARIZE('Table','Table'[Items]),'Table 2')
Step3: Create a column
value =
VAR a =
RELATED ( 'Table'[Amount] )
* (
MONTH ( CONVERT ( LEFT ( 'Gnew_table'[date], 3 ) & " 1", DATETIME ) )
- MONTH (
CONVERT ( LEFT ( RELATED ( 'Table'[Start of Saving] ), 3 ) & " 1", DATETIME )
) + 1
)
RETURN
IF ( a > 0, a, BLANK () )
Step4: Sort by Index column
Last create a matrix ,The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @John_Dozen34999 ,
According to your description, you could create table to output 12 months and use GENERATE() to connect 2 tables,
then create a column of calculated values, and finally put it in matrix and sort by index. the following formula to create :
Step1: Enter data to create a table and add index column:
Step2: New table
Gnew_table = GENERATE(SUMMARIZE('Table','Table'[Items]),'Table 2')
Step3: Create a column
value =
VAR a =
RELATED ( 'Table'[Amount] )
* (
MONTH ( CONVERT ( LEFT ( 'Gnew_table'[date], 3 ) & " 1", DATETIME ) )
- MONTH (
CONVERT ( LEFT ( RELATED ( 'Table'[Start of Saving] ), 3 ) & " 1", DATETIME )
) + 1
)
RETURN
IF ( a > 0, a, BLANK () )
Step4: Sort by Index column
Last create a matrix ,The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello!
is it possible to apply the frequency by quarterly or biannually?
Thanks! this resolved my problem. You're a life saver 🙂
there are a few things going on in this calculation, the first is to deal with these items one at a time, even for the total, for this, I have used the SUMX function, then we need to calculate the date difference between the [Start of savings date] and the current [year month](or period). Combining these two we get the following calc.
Measure1 =
SUMX (
CALCULATETABLE (
VALUES ( 'items'[Item] ),
ALL ( Dates )
),
VAR _EndOFDateRange =
MAX ( 'Dates'[Date] )
VAR _StartDate =
CALCULATE (
MIN ( 'items'[Start of Savings] ),
ALL ( 'Dates' )
)
VAR _NumberOfMonths =
DATEDIFF (
_StartDate,
_EndOFDateRange,
MONTH
) + 1
VAR _Amount =
CALCULATE (
SUM ( 'items'[Amount] ),
ALL ( Dates )
)
RETURN
IF (
_NumberOfMonths > 0,
_NumberOfMonths * _Amount,
BLANK ()
)
)
The image below shows that this aligns with what you have, however, there are a few limitations you would only ever be able to have a single amount and date for a given item.
@John_Dozen34999 , Try a measure like
measure =
var _min = minx(allselected('Date'),'Date'[Date])
var _val = minx(allselected('Table'),'Table'[value])
return
_val1 * datediff(_min, max('Date'[Date], month))
thanks for quick response.
i updated my problem.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |