Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have a table with dates, "growth rates" and product keys. See snapshot below:
Each product category may have a different "growth rate" for each date, though you can only see Jan 2022 in this snapshot (it goes out 24 months).
Here is my equation. Unfortunately it looks like it sums up the entire calculation with my formula below.
Compounding Value =
VAR LatestDate =
MAX ( 'Collection Growth Table'[date] )
VAR UnfilteredTable =
ALL ( 'date table'[date] )
RETURN
CALCULATE (
PRODUCTX ( 'Collection Growth Table', 1 + 'Collection Growth Table'[Value] ),
FILTER (
ALL ( 'Collection Growth Table' ),
'Collection Growth Table'[Date] <= LatestDate
)
)
Ideally this would represent the compounding growth rate of each Quickname and Date. Any help would be greatly appreciated!
Solved! Go to Solution.
Ok. Please try
Compounding Value =
VAR DateQuickNameTable =
CALCULATETABLE (
'date table',
ALLEXCEPT ( 'date table', 'date table'[Quickname] )
)
VAR CurrentDate = 'Collection Growth Table'[date]
RETURN
PRODUCTX (
FILTER ( DateQuickNameTable, 'Collection Growth Table'[Date] <= CurrentDate ),
1 + 'Collection Growth Table'[Value]
)
Hi @tkfisher
"Ideally this would represent the compounding growth rate of each Quickname and Date."
My understanding is that, this is a unique column so what is the use of PRODUCTX? Why not "for each quick name" only? And where is the category you have mentioned at the begining of your post?
I might be mistaken but you may try
Compounding Value =
VAR DateQuickNameTable =
CALCULATETABLE (
'date table',
ALLEXCEPT ( 'date table', 'date table'[Quickname] )
)
VAR LatestDate =
MAXX ( DateQuickNameTable, 'Collection Growth Table'[date] )
RETURN
PRODUCTX (
FILTER ( DateQuickNameTable, 'Collection Growth Table'[Date] <= LatestDate ),
1 + 'Collection Growth Table'[Value]
)
Apologies, should have been clearer. I have been trying a few things and so my DAX was getting mixed together. Here is realistically where I am at now:
Compounding Value =
VAR LatestDate =
MAX ( 'Collection Growth Table'[date] )
VAR UnfilteredTable =
ALL ( 'date table' )
RETURN
CALCULATE (
PRODUCTX ( 'Collection Growth Table', 1 + 'Collection Growth Table'[Value] ),
FILTER (
ALL ( 'Collection Growth Table' ),
'Collection Growth Table'[Date] <= LatestDate
)
)
Filtering on just one Quickname (apologies, I said category before but meant Quickname) shows my issue more clearly:
What I would like is a column that compounds the 'Value' column month over month, but does it separately for each Quickname.
Sorry was wrong on my DAX again.... here you go
Compounding Value =
VAR LatestDate =
MAX ( 'Collection Growth Table'[date] )
VAR UnfilteredTable =
ALL ( 'Collection Growth Table' )
RETURN
CALCULATE (
PRODUCTX ( 'Collection Growth Table', 1 + 'Collection Growth Table'[Value] ),
FILTER (
ALL ( 'Collection Growth Table' ),
'Collection Growth Table'[Date] <= LatestDate
)
)
Ok. Please try
Compounding Value =
VAR DateQuickNameTable =
CALCULATETABLE (
'date table',
ALLEXCEPT ( 'date table', 'date table'[Quickname] )
)
VAR CurrentDate = 'Collection Growth Table'[date]
RETURN
PRODUCTX (
FILTER ( DateQuickNameTable, 'Collection Growth Table'[Date] <= CurrentDate ),
1 + 'Collection Growth Table'[Value]
)
This works, thank you!
User | Count |
---|---|
57 | |
21 | |
18 | |
16 | |
12 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |