Date measures calculated on Date filter

[Acyrus1992]

Hi 

I am new to Power BI, and am now trying to migrate all my Excel reports into Power BI.

I have a measure to perform a count of values.

These values will then change based on teh date filter.

I now need to create a second measured value like above - but it needs to be a math date calculation that subtracts a week from the selected range (thus giving you the Previous Weeks volume) while displaying the present date filtered count too

Could someone please help me

Cheers

[v-yulgu-msft · Accepted solution]

Hi @Acyrus1992,

Suppose there are two columns in source table Table[Date] and Table[Volume].

First, you should create a new table(in my test, it's named as Table2). Also, you need a calendar table (in my test, it's 'dim'). Remember to create a relationship between Table2 and dim. In Table2, create a calculated column to display the date a week ago.

Table2 =
SELECTCOLUMNS ( 'Table1', "date", 'Table1'[Date] )
7 days ago = DATEADD(dim[Date],-7,DAY) 

Then, create below measures:

Min = MIN('Date column'[7 days ago])
Max = MAX('Date column'[7 days ago])

Count volume for this week =
CALCULATE (
    COUNT ( 'Table1'[Used service] ),
    FILTER (
        'Table1',
        'Table1[Date] >= MIN ( 'Table2'[date] )
            && 'Table1'[Date] <= MAX ( 'Table2'[date] )
    )
)
Count volume for last week =
CALCULATE (
    COUNT ( 'Table1'[Used service] ),
    FILTER (
        'Table1',
        'Table1'[Date] >= [Min]
            && 'Table1'[Date] <= [Max]
    )
)

In slicer, you should add Table2[date] into field section. Dislay measures [Count volume for this week] and [Count volume for last week] in two card visual.

Best regards,
Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @Acyrus1992,

Suppose there are two columns in source table Table[Date] and Table[Volume].

First, you should create a new table(in my test, it's named as Table2). Also, you need a calendar table (in my test, it's 'dim'). Remember to create a relationship between Table2 and dim. In Table2, create a calculated column to display the date a week ago.

Table2 =
SELECTCOLUMNS ( 'Table1', "date", 'Table1'[Date] )
7 days ago = DATEADD(dim[Date],-7,DAY) 

Then, create below measures:

Min = MIN('Date column'[7 days ago])
Max = MAX('Date column'[7 days ago])

Count volume for this week =
CALCULATE (
    COUNT ( 'Table1'[Used service] ),
    FILTER (
        'Table1',
        'Table1[Date] >= MIN ( 'Table2'[date] )
            && 'Table1'[Date] <= MAX ( 'Table2'[date] )
    )
)
Count volume for last week =
CALCULATE (
    COUNT ( 'Table1'[Used service] ),
    FILTER (
        'Table1',
        'Table1'[Date] >= [Min]
            && 'Table1'[Date] <= [Max]
    )
)

In slicer, you should add Table2[date] into field section. Dislay measures [Count volume for this week] and [Count volume for last week] in two card visual.

Best regards,
Yuliana Gu

[Acyrus1992]

Hi @v-yulgu-msft

Thank you for the response.

Before you replied, I added in some additional fields that Im hoping will help me achieve this easier.

I have the year Week number in the table too now.

So to get the count of rows for this week - I have written this:

Tickets Raised = CALCULATE(
COUNTROWS('Platform Data')
,FILTER('Platform Data'
,'Platform Data'[Resolution SLA] = "SLA Met"),FILTER('Platform Data','Platform Data'[Data Type] = "Incident" || 'Platform Data'[Data Type] = "Service Request"))

What this is doing, is only counting the rows in the dataset where the Resolution SLA is true, and where the Data Type is either value1 or value2

This gives me the correct figure for each week - when I select the week value.

Now what I was hoping to acheive - is to write the same DAX in adding in a rule stating "Selected Week filter Value" - 1.

Effectivly - Last Weeks value for Week 18 wuold then be 74

Does this make sence to you?

Im hoping it does haha

I am unable to find the right syntax and code to get it right yet

Any help would be greatly appreciated

Thank you

[v-yulgu-msft · Accepted solution]

Hi @Acyrus1992,

If you want to get the previous week value (74) when selecting current week number (18), you should also refer to the advice in my original reply.

First, you should create an extra table. And add a new column which display the last week number.

Table week =
SELECTCOLUMNS ( 'Platform Data', "weekNo", 'Platform Data'[Week Number] )

Last week No = 'Table week'[weekNo]-1 

Drag Table week[weekNo] into slicer.

Then, create measures like which is added into chart visual.

Tickets Raised =
CALCULATE (
    COUNTROWS ( 'Platform Data' ),
    FILTER ( 'Platform Data', 'Platform Data'[Resolution SLA] = "SLA Met" ),
    FILTER (
        'Platform Data',
        'Platform Data'[Data Type] = "Incident"
            || 'Platform Data'[Data Type] = "Service Request"
    ),
   FILTET(
   'Platform Data'[Week Number]=MAX('Table week'[Last week No]))
)

Best regards,
Yuliana Gu