The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
hi,
I'm calculating for a contest. In order to be "qualified" for the contest, participants needs to have 12 months of sales in 2020.
my database generates data like this
Participants | date.year month | products | sales |
A | 1 Jan 2020 | x | 40 |
A | 1 Feb 2020 | x | 20 |
A | 1 Feb 2020 | y | 20 |
B | 1 Jan 2020 | x | 50 |
so to achieve the purpose, I need to
1. sum the sales of participant per month (because each participant sells multiple products), then
2. Count number of months in 2020 whereby sales>0 for each particpant
3. if count number = 12, participant is qualified, if not not qualified
so this new column created will make the database look like this:
Participants | date.year month | products | sales | qualification |
A | 1 Jan 2020 | x | 40 | qualified |
A | 1 Feb 2020 | x | 20 | qualified |
A | 1 Feb 2020 | y | 20 | qualified |
B | 1 Jan 2020 | x | 50 | not qualified |
Thanks to help!
AL
Solved! Go to Solution.
Hi @AdaL02 ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a calculated column to get the yearmonth
YearMonth = CONCATENATE ( YEAR ( 'Table'[Date] ), FORMAT ( 'Table'[Date], "MM" ) )
2. Get the status which is qualified or not
Method 1: First create a measure to get the sum of sales, then create a measure to get the status
Sum of sales = SUM('Table'[sales])
qualification =
VAR _selyear =
SELECTEDVALUE ( 'Table'[Date].[Year] )
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[YearMonth] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Participants] = SELECTEDVALUE ( 'Table'[Participants] )
&& YEAR ( 'Table'[Date] ) = _selyear
&& [Sum of sales] > 0
)
)
RETURN
IF ( _count = 12, "qualified", "not qualified" )
Method 2: Create a calculated column to get the status
Column =
VAR _selyear =
SELECTEDVALUE ( 'Table'[Date].[Year] )
VAR _sales =
CALCULATE (
SUM ( 'Table'[sales] ),
FILTER (
'Table',
'Table'[Participants] = EARLIER ( 'Table'[Participants] )
&& YEAR ( 'Table'[Date] ) = _selyear
&& 'Table'[YearMonth] = EARLIER ( 'Table'[YearMonth] )
)
)
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[YearMonth] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Participants] = EARLIER ( 'Table'[Participants] )
&& YEAR ( 'Table'[Date] ) = _selyear
&& _sales > 0
)
)
RETURN
IF ( _count = 12, "qualified", "not qualified" )
Best Regards
Hi @AdaL02 ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a calculated column to get the yearmonth
YearMonth = CONCATENATE ( YEAR ( 'Table'[Date] ), FORMAT ( 'Table'[Date], "MM" ) )
2. Get the status which is qualified or not
Method 1: First create a measure to get the sum of sales, then create a measure to get the status
Sum of sales = SUM('Table'[sales])
qualification =
VAR _selyear =
SELECTEDVALUE ( 'Table'[Date].[Year] )
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[YearMonth] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Participants] = SELECTEDVALUE ( 'Table'[Participants] )
&& YEAR ( 'Table'[Date] ) = _selyear
&& [Sum of sales] > 0
)
)
RETURN
IF ( _count = 12, "qualified", "not qualified" )
Method 2: Create a calculated column to get the status
Column =
VAR _selyear =
SELECTEDVALUE ( 'Table'[Date].[Year] )
VAR _sales =
CALCULATE (
SUM ( 'Table'[sales] ),
FILTER (
'Table',
'Table'[Participants] = EARLIER ( 'Table'[Participants] )
&& YEAR ( 'Table'[Date] ) = _selyear
&& 'Table'[YearMonth] = EARLIER ( 'Table'[YearMonth] )
)
)
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[YearMonth] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Participants] = EARLIER ( 'Table'[Participants] )
&& YEAR ( 'Table'[Date] ) = _selyear
&& _sales > 0
)
)
RETURN
IF ( _count = 12, "qualified", "not qualified" )
Best Regards
actually i hardcoded the _selyear = 2020, it works. of course, feel free to add solution on dynamic calculation but in the meanwhile, the DAX works! Thank you for the support!
hi @v-yiruan-msft,
Thanks for your explanation. I tried the solution but it shows everything "not qualified" in my actual database... So supposedly something is not right...
Now in my actual database, I do have many years of data (perhaps 5 years of historical and 5 years of forecast). Qualification is to take place only if you have 12 months of sales (regardess what product(s) you are selling) in 2020. Will there be an impact on _selyear variable in this case? By doing selectedvalue, all the years (as long as there is a value) will be selected. Hence whe it comes to return If part, no one has just 12 values to be qualified. Wonder how should I adjust the formula? It's ok to hardcode 2020. But also good to have dynamic coding. Qualification is always based on the current year - 1, 12 months of sales for every single month.
Ada
Create a measure like:
Qualified =
IF (
CALCULATE (
COUNTROWS ( Sales ),
FILTER (
ALLEXCEPT ( Sales, Sales[Participants] ),
Sales[sales] > 0
&& YEAR ( Sales[Date] ) = YEAR ( Sales[Date] )
)
) = 12,
"Qualified",
"Not Qualified"
)
Please check this sample file
hi Rafaei,
Thanks for the explanation. I think the sample file was not right as May or APril of the month mentioned has 0 sales, yet the formula qualifies the participant. Also there are multiple products per each participant. Hence counting rows perhaps will lead to all participants not qualified....
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
246 | |
119 | |
114 | |
86 | |
70 |