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.
I got this sales table
InvoiceDate
Client ID
Revenue
And from that have a measure that gets the monthly revenue by using SUM in the Revenue column from the table above and this how it looks
Client ID | Month/Yr | Revenue
1 | May 2020 | 411
1 | Apr 2020 | 222
1 | Jun 2020 | 133
1 | Jul 2020 | 432
2 | Jan 2020 | 333
2 | Feb 2020 | 322
2 | Mar 2020 | 434
3 | Feb 2020 | 132
3 | Apr 2020 | 123
3 | Aug 2020 | 434
I am having a problem creating the measure get the value of the Revenue Total for the first month for each client ID whatever the value in the monthy slicer is:
If the user choses Aug 2020 the result should be like this:
Client ID | 1st Revenue | Current
1 | 411 | blank
2 | 333 | blank
3 | 132 | 434
If the user choses Apr 2020 the result shoul be like this:
Client ID | 1st Revenue | Current
1 | 411 | 222
2 | 333 | blank
3 | 132 | 123
The value for the 1st Revenue will only display if the selected value for the Year slicer is the same as the year of the 1st invoice date.
Thank you in advance!
Solved! Go to Solution.
Hi @giorajo ,
You can follow the below steps to achieve it:
1. Create a client table
Clients = VALUES('Table'[Client ID ])
2. Create a measure to get 1st revenue per client
1st Revenue =
VAR _minMonth =
CALCULATE (
MIN ( 'Table'[Month/Yr] ),
FILTER ( ALL ( 'Table' ), 'Table'[Client ID ] = MAX ( 'Clients'[Client ID ] ) )
)
RETURN
CALCULATE (
MAX ( 'Table'[Revenue] ),
FILTER (
ALL ( 'Table' ),
'Table'[Client ID ] = MAX ( 'Clients'[Client ID ] )
&& 'Table'[Month/Yr] = _minMonth
)
)
3. Create a measure to get current revenue per client
Current =
CALCULATE (
MAX ( 'Table'[Revenue] ),
FILTER ( 'Table', 'Table'[Client ID ] = MAX ( 'Clients'[Client ID ] ) )
)
Best Regards
Rena
@giorajo , is this raw data, or do you have a date in the raw data.
If these is no date create a date like this and join with date table
Date = "01 " & [Month/Yr]
and create a measure like
calculate(sum(Revenue), filter(all(Date), format(Date[date],"MMM YYYY") = format(min(Date[Date]),"MMM YYYY")))
select month measure would be
sum(Revenue)
Thank you but I actually tried that already and the value 1st revenue is changed whenever the month slicer is changed.
The table above shows the result of the statement you provided and the table below is the result I am expecting:
Hi @giorajo ,
You can follow the below steps to achieve it:
1. Create a client table
Clients = VALUES('Table'[Client ID ])
2. Create a measure to get 1st revenue per client
1st Revenue =
VAR _minMonth =
CALCULATE (
MIN ( 'Table'[Month/Yr] ),
FILTER ( ALL ( 'Table' ), 'Table'[Client ID ] = MAX ( 'Clients'[Client ID ] ) )
)
RETURN
CALCULATE (
MAX ( 'Table'[Revenue] ),
FILTER (
ALL ( 'Table' ),
'Table'[Client ID ] = MAX ( 'Clients'[Client ID ] )
&& 'Table'[Month/Yr] = _minMonth
)
)
3. Create a measure to get current revenue per client
Current =
CALCULATE (
MAX ( 'Table'[Revenue] ),
FILTER ( 'Table', 'Table'[Client ID ] = MAX ( 'Clients'[Client ID ] ) )
)
Best Regards
Rena
Thank you, @amitchandak. This is raw data. the table structure is Invoice Date | Company ID | Revenue.
I need to display the monthly totals for the revenue (should change depending on the month slicer) and the 1st revenue for the company's 1st month (always the same whatever the month is) where I am having problems.
@giorajo , Update the last post. please check if that can help
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |