Display Week end (Friday) or Month End results based on filter selected

[shiva_gri]

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.

[v-haibl-msft]

@shiva_gri

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

[shiva_gri]

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 

[v-haibl-msft]

@shiva_gri

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

[shiva_gri]

Hi Herbert

The Goal comes from the same table or I can have another view to lookup that Goal value based on agent name

[v-haibl-msft]

@shiva_gri

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

[shiva_gri]

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

[v-haibl-msft]

@shiva_gri

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

[shiva_gri]

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])
)

[v-haibl-msft]

@shiva_gri

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

[shiva_gri]

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?

[v-haibl-msft]

@shiva_gri

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

[AlbertBISM]

There is a function called ENDOFTHEMONTH, ENDOFTHEQUARTER to achieve this.

[shiva_gri]

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