Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi all,
i need to create such calcualtion, based on the data below:
ID | Close Q | Close M | Est.Start Date | Est.End Date | Estimated Close Date | Segment | Total Sales Value (USD) | 2023-Sep (USD) | 2023-Oct (USD) | 2023-Nov (USD) | FY2024-Q1 (USD) | 2023-Dec (USD) | 2024-Jan (USD) | 2024-Feb (USD) | FY2024-Q2 (USD) | 2024-Mar (USD) | 2024-Apr (USD) | 2024-May (USD) | FY2024-Q3 (USD) | 2024-Jun (USD) | 2024-Jul (USD) | 2024-Aug (USD) | FY2024-Q4 (USD) | FY2024-Total (USD) | 2024-Sep (USD) | 2024-Oct (USD) | 2024-Nov (USD) | FY2025-Q1 (USD) | 2024-Dec (USD) | 2025-Jan (USD) | 2025-Feb (USD) | FY2025-Q2 (USD) | 2025-Mar (USD) | 2025-Apr (USD) | 2025-May (USD) | FY2025-Q3 (USD) | 2025-Jun (USD) | 2025-Jul (USD) | 2025-Aug (USD) | FY2025-Q4 (USD) | FY2025-Total (USD) | Future Revenue (USD) | W 2023-Sep (USD) | W 2023-Oct (USD) | W 2023-Nov (USD) | W FY2024-Q1 (USD) | W 2023-Dec (USD) | W 2024-Jan (USD) | W 2024-Feb (USD) | W FY2024-Q2 (USD) | W 2024-Mar (USD) | W 2024-Apr (USD) | W 2024-May (USD) | W FY2024-Q3 (USD) | W 2024-Jun (USD) | W 2024-Jul (USD) | W 2024-Aug (USD) | W FY2024-Q4 (USD) | W FY2024-Total (USD) | W 2024-Sep (USD) | W 2024-Oct (USD) | W 2024-Nov (USD) | W FY2025-Q1 (USD) | W 2024-Dec (USD) | W 2025-Jan (USD) | W 2025-Feb (USD) | W FY2025-Q2 (USD) | W 2025- Mar(USD) | W 2025-Apr (USD) | W2025-May (USD) | W FY2025-Q3 (USD) | W 2025-Jun (USD) | W 2025-Jul (USD) | W 2025-Aug (USD) | W FY2025-Q4 (USD) | W FY2025-Total (USD) | W Future Revenue (USD) |
A000374210 | Q4 | June | 01/03/2024 | 31/05/2024 | 02/06/2024 | S1 | 131979,05 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 41993,34 | 43993,02 | 45992,7 | 131979,06 | 0 | 0 | 0 | 0 | 131979,06 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4199,33 | 4399,3 | 4599,27 | 13197,91 | 0 | 0 | 0 | 0 | 13197,91 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
A000374325 | Q3 | May | 27/05/2024 | 26/07/2024 | 17/05/2024 | S2 | 21253,91 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2361,55 | 2361,55 | 9446,18 | 9446,18 | 0 | 18892,36 | 21253,91 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1416,93 | 1416,93 | 5667,71 | 5667,71 | 0 | 11335,42 | 12752,35 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
A000375012 | Q4 | June | 25/06/2024 | 16/12/2024 | 16/06/2024 | S3 | 541758,77 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 17336,28 | 99683,61 | 95349,54 | 212369,43 | 212369,43 | 91015,47 | 99683,61 | 91015,47 | 281714,55 | 47674,77 | 0 | 0 | 47674,77 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 329389,32 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3467,26 | 19936,72 | 19069,91 | 42473,89 | 42473,89 | 18203,09 | 19936,72 | 18203,09 | 56342,91 | 9534,95 | 0 | 0 | 9534,95 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 65877,86 | 0 |
A000375098 | Q4 | July | 26/07/2024 | 26/10/2024 | 26/07/2024 | S3 | 100000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6060,61 | 33333,33 | 39393,94 | 39393,94 | 31818,18 | 28787,88 | 0 | 60606,06 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 60606,06 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 606,06 | 3333,33 | 3939,39 | 3939,39 | 3181,82 | 2878,79 | 0 | 6060,61 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6060,61 | 0 |
A000375131 | Q1 | November | 07/11/2024 | 08/01/2025 | 07/11/2024 | S4 | 52791,62 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 19943,5 | 19943,5 | 25809,23 | 7038,89 | 0 | 32848,12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 52791,62 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 7977,4 | 7977,4 | 10323,69 | 2815,56 | 0 | 13139,25 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 21116,65 | 0 |
A000375267 | Q1 | September | 20/09/2024 | 23/01/2025 | 20/09/2024 | S2 | 527916,21 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 41060,15 | 134911,92 | 123180,45 | 299152,52 | 129046,18 | 99717,5 | 0 | 228763,68 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 527916,2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4106,02 | 13491,19 | 12318,05 | 29915,25 | 12904,62 | 9971,75 | 0 | 22876,37 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 52791,62 | 0 |
Eventual output should be the following matrix:
S1 | S2 | S3 | S4 | S5 | S6 | |
L | ||||||
M | ||||||
S | ||||||
XS |
L | > 2M Revenue next rolling 12 months |
M | 500k-2M Revenue next rolling 12 months |
S | <500k Revenue next rolling 12 months |
XS | <500k Revenue next rolling 6 months |
I have prepared such calcuation, but the column names with Q1-Q4 are hard coded. Is it possible to do it dynamic with DAX or within Power Query?
Any help appreciated!
Solved! Go to Solution.
Hi @RT6 ,
It looks like you need L,M,S,XS data, but I'm only looking at M data. In any case, you may need to use to unpivot column.
Best regards,
Community Support Team_ Scott Chang
Hi v-tianyich-msft,
thanks your response. I have unpivotted and selcted only the months i need. I was able to calculate as well
An ideas? Thanks in advance!"
Hi @RT6 ,
Try changing the order in switch, using the following expression:
Company Size Category =
VAR Rolling12M = [Rolling 12M Revenue]
VAR Rolling6M = [Rolling 6M Next]
RETURN
SWITCH(
TRUE(),
Rolling12M >= 500000 && Rolling12M <= 2000000, "M",
Rolling12M > 2000000, "L",
Rolling6M < 500000, "S",
Rolling12M < 500000, "XS",
"Uncategorized"
)
Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi V-tianyich.msft,
thanks for your fast response. Changing order gives the same results. I assume it calcualted not the sum of the Rolling Revenue, but takes the value only for 1 month, in this case each of them is less than 500,000, therefore there are no L or S and it shows it as XS. The solution was to include ALLEXCEPT filter into calcualtion Company Size Category_Total =
VAR Rolling12M = CALCULATE(
[Rolling 12M Revenue],
ALLEXCEPT('_Fact_', '_Fact_'[Opportunity ID]) // Removing all filters except for Opportunity ID
)
VAR Rolling6M = CALCULATE(
[Rolling 6M Next],
ALLEXCEPT('_Fact_', '_Fact_'[Opportunity ID]) // Similarly, for the 6-month calculation
)
RETURN
SWITCH(
TRUE(),
Rolling12M >= 500000 && Rolling12M <= 2000000, "M",
Rolling12M > 2000000, "L",
Rolling6M < 500000, "XS",
Rolling12M < 500000, "S",
"Uncategorized"
)
Hi @RT6 ,
It looks like you need L,M,S,XS data, but I'm only looking at M data. In any case, you may need to use to unpivot column.
Best regards,
Community Support Team_ Scott Chang
User | Count |
---|---|
97 | |
87 | |
78 | |
74 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |