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.
Hello,
I'm trying to add 'datesbetween' for the following formular. However I could not get any value.
Could you please support how to modify it?
Column 2 = VAR sales_total =
calculate([Actual Total],DATESBETWEEN(DateDimensions[Date],nextday(SAMEPERIODLASTYEAR(LASTDATE(DateDimensions[Date]))),LASTDATE(DateDimensions[Date])))
RETURN
IF(
AND (sales_total >= 0, sales_total < 1000 ),
"Minor",
IF(
AND ( sales_total > 5000, sales_total < 20000 ),
"General",
) )
Solved! Go to Solution.
Hi @H1r0ka,
As we know, measures could not be added to Goup / X-axis in visuals, So here we need to create a calculated column in SalesTable.
measu1 = VAR sales_total = calculate([Actual Total],DATESBETWEEN(DateDimensions[Date],nextday(SAMEPERIODLASTYEAR(LASTDATE(DateDimensions[Date]))),LASTDATE(DateDimensions[Date]))) RETURN IF( AND (sales_total >= 0, sales_total < 50 ), "Minor", IF( AND ( sales_total > 50, sales_total < 20000 ), "General" ) )
For more details, please check the attachment.
Regards,
Frank
Hi @H1r0ka,
Could you please share you sample data to me?
Regards,
Frank
Hello v-frfei-msft,
Thank you very much for your support.
Datedimensions is date table.
Date | Day | Month | Year |
1-Sep-18 | 1 | 9 | 2018 |
2-Sep-18 | 2 | 9 | 2018 |
3-Sep-18 | 3 | 9 | 2018 |
4-Sep-18 | 4 | 9 | 2018 |
5-Sep-18 | 5 | 9 | 2018 |
SalesTable contains sales date.
[Actual] is calculationfield of ( [Local Price]/[Rate]) and [Actual Total] is calculation field of (Sum [Actual]).
SalesKey | Date | CustomerID | Local Price | Rate | Actual |
001 | 1-Sep-17 | 9901 | 2000 | 110 | 18.2 |
002 | 1-Oct-17 | 9902 | 3000 | 115 | 26.1 |
003 | 3-Sep-18 | 9901 | 5000 | 120 | 41.7 |
004 | 3-Sep-18 | 9903 | 10000 | 120 | 83.3 |
005 | 6-Sep-18 | 9903 | 3000 | 120 | 25.0 |
Rank Table is list of rank.
Rank | Type |
0>=, 1000< | Minor |
5000>, <20000 | General |
Thank you in advance,
Hi @H1r0ka,
Based on your data, I made one sample as below. Actually that should be the issue of your formula, you need to remove the last comma of your formual.
measu = VAR sales_total = CALCULATE ( [Actual Total], DATESBETWEEN ( DateDimensions[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( DateDimensions[Date] ) ) ), LASTDATE ( DateDimensions[Date] ) ) ) RETURN IF ( AND ( sales_total >= 0, sales_total < 50 ), "Minor", IF ( AND ( sales_total > 50, sales_total < 20000 ), "General" ) )
For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share your excepted result to me.
Regards,
Frank
Hello
Thank you very much for your solution. The table is wish to create.
I also need the following fannel chart with the formula data.
How to creat that?
Hi @H1r0ka,
As we know, measures could not be added to Goup / X-axis in visuals, So here we need to create a calculated column in SalesTable.
measu1 = VAR sales_total = calculate([Actual Total],DATESBETWEEN(DateDimensions[Date],nextday(SAMEPERIODLASTYEAR(LASTDATE(DateDimensions[Date]))),LASTDATE(DateDimensions[Date]))) RETURN IF( AND (sales_total >= 0, sales_total < 50 ), "Minor", IF( AND ( sales_total > 50, sales_total < 20000 ), "General" ) )
For more details, please check the attachment.
Regards,
Frank
Hello,
Thank you very much for your support! I got the funnel chart.
Hi,
Please show your expected result.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |