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.
Hi,
I am new to Power BI. I have Year, Quarter, Month and Week Numbers (W1,W2,W3..) Filters. Need to sum Revenue for each Candidates and display in Table chart based on below conditions. The table has records for all days in a week
1) When Week is selected it should display only Week end (Friday) sum and not the sum of all revenue for whole week
2) When Month is selected it should display only end of month's (31-Mar-2016) sum and not the sum of all revenue for whole month
3) When Quarter is selected it should display only end of quarter day's (30-Sep-2016) sum and not the sum of all revenue for whole quarter
Could somebody give me a way how it can be achieved?
When I have this in Powerbi. It displays all data for whole week or month or year and not the last day's sum.
In this scenario, we can create three measures to calculate the weekend, monthend and quarterend separately. But we need to create another table (Table2 in following screenshot) and make it as a slicer, in that way, we can choose which measure should be used.
Then we need to create following 4 measures.
QuarterEndRevenue = CALCULATE ( SUM ( Table1[Revenue] ), FILTER ( ALLSELECTED ( Table1 ), Table1[IsQuarterend] = "Y" ), VALUES ( Table1[Agent] ) )
MonthEndRevenue = CALCULATE ( SUM ( Table1[Revenue] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Ismonthend] = "Y" ), VALUES ( Table1[Agent] ) )
WeekEndRevenue = CALCULATE ( SUM ( Table1[Revenue] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Isweekend] = "Y" ), VALUES ( Table1[Agent] ) )
EndRevenue = SWITCH ( TRUE (), SUM ( Table2[Index] ) = 1, [QuarterEndRevenue], SUM ( Table2[Index] ) = 2, [MonthEndRevenue], SUM ( Table2[Index] ) = 3, [WeekEndRevenue] )
Drag the EndRevenue measure into Values field of Table chart. Select the Type and select Year/Quarter/Month/Week in slicers, we should be able to get the expected result now.
I’ve also uploaded my PBIX file here for reference.
Best Regards,
Herbert
Hi Herbert
Thanks a lot. It was very helpful. I have another lookup field from another table along with Revenue which needs to remain constant and not aggregated. When I add that as column in Table 1 it displays for daily. But when monthly or weekly it aggregates and shows the sum rather than constant amount. If i select don't aggregate then it displays individual record values rather than single record for a month or week
Eg
Agent Rev Goal Date
A 100 140 01/01/2016
A 150 140 02/01/2016
When month selected it shows
A 250 280 January
or when not set to Don't aggregate
A 100 140 01/01/2016
A 150 140 02/01/2016
Is there anyway to display it as
A 250 140 January
Which table does the Goal column belong to? What is the relationship between the Goal table and Table1? Please give a sample of Goal table if possible.
Best Regards,
Herbert
Hi Herbert
The Goal comes from the same table or I can have another view to lookup that Goal value based on agent name
You can try to delete the Day hierarchy from Table Values field as below. If keep the Day in it, the date will be distinct and there will be multiple results returned.
Best Regards,
Herbert
Hi Herbert,
Thanks a lot. It worked well. I have another query. I need revenue difference calculated from today's data with previously loaded data. The issue is that there will be gaps in load dates. Example below. Is there any way to find the immediate predecessor date for a particular load date
Agent Revenue Last Load date Revenue Diff (today's revenue - previous day loaded revenue)
A 300 03/10/2016
A 400 05/10/2016 100
A 600 07/10/2016 200
Is the date table like you posted as below?
If yes, you can create a measure with following formula. We get the pre load date and lookup the revenue of the same agent.
Diff_Revenue = VAR PreDate = CALCULATE ( MAX ( Table3[Last Load date] ), FILTER ( ALLSELECTED ( Table3 ), Table3[Last Load date] < MAX ( Table3[Last Load date] ) ), VALUES ( Table3[Agent] ) ) VAR PreDateAgent = LASTNONBLANK ( Table3[Agent], Table3[Agent] ) VAR PreDateRevenue = LOOKUPVALUE ( Table3[Revenue], Table3[Last Load date], PreDate, Table3[Agent], PreDateAgent ) RETURN ( IF ( PreDateRevenue <> BLANK (), CALCULATE ( MAX ( Table3[Revenue] ) ) - PreDateRevenue ) )
Best Regards,
Herbert
Hi Herbert
I am attaching the pbix file i have created. As you could see it returns the first previous max date for all records
Formula I am using to get previous date
CALCULATE (
MAX ( REV[date] ),
FILTER (
ALLSELECTED ( REV ),
REV[date] < MAX( REV[date] )
),
VALUES ( REV[Agent])
)
From the screenshot, it seems that you’re using the formula to create a calculated column. My previous formula is for calculated measure. If you want to create a column, you can try with following formula to get the pre date.
BTW, I didn’t find your PBIX here. You can upload it to some online file share sites like OneDrive if you want to share it.
PD = CALCULATE ( MAX ( Table3[Last Load date] ), FILTER ( Table3, EARLIER ( Table3[Last Load date] ) > Table3[Last Load date] && EARLIER ( Table3[Agent] ) = Table3[Agent] ) )
Best Regards,
Herbert
Hi Herbert
The Pre date value I get is next predecessor for whole set of date and not for a particular date. For example I am getting 5/10/2016 for all record rather than 03/10/2016 for 05/10/2016 and 05/10/2016 for 07/10/2016. Do I need to create any index or something?
Is your table same as I posted in last reply? If possible, could you please give a screenshot of your data table and the result shown in in visual?
Best Regards,
Herbert
There is a function called ENDOFTHEMONTH, ENDOFTHEQUARTER to achieve this.
Thanks. I already have the end of quarter or month calculated on another table and a flag says it is month end or quarter end or weekend. The data looks like this.
Agent Revenue Date Isweekend Ismonthend IsQuarteend
A 10 31-Aug-2016 N Y N
B 20 26-Aug-2016 Y N N
A 20 19-Aug-2016 Y N N
I have filters Year, Month name, Week. When I select month name (August) in filter it lists all revenue for Aug (3 records). I need to list only 31-Aug-2016 (first record). What DAX formula should be used in measure to get only last day of the month record based on IsMonthEnd = Y
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |