WEEK CALCULATIONS

[Anonymous]

Hi,

I have a table like below,

Date	Network Name	Office Name	Count
7-Jun	N1	OFF 1	4
7-Jun	N2	OFF 3	3
6-Jun	N1	OFF 2	5
6-Jun	N2	OFF 4	5
5-Jun	N1	OFF 1	2
5-Jun	N2	OFF 3	3
4-Jun	N1	OFF 2	1
4-Jun	N2	OFF 4	1
3-Jun	N1	OFF 1	4
3-Jun	N2	OFF 3	1
2-Jun	N1	OFF 2	4
2-Jun	N2	OFF 4	2
1-Jun	N1	OFF 1	3
1-Jun	N2	OFF 3	5
31-May	N1	OFF 2	1
31-May	N2	OFF 4	2
30-May	N1	OFF 1	5
30-May	N2	OFF 3	2
29-May	N1	OFF 2	2
29-May	N2	OFF 4	2
28-May	N1	OFF 1	3
28-May	N2	OFF 3	2

I have written few calculated columns,

Week Start date = 'Table'[Date]+-1*WEEKDAY('Table'[Date],2)+1

Week End date = 'Table'[Date]+ 7-1*WEEKDAY('Table'[Date],2)

Week Number = WEEKNUM('Table'[Date],2)

Year = YEAR('Table'[Date])

Week = if('Table'[Week Number]<10,'Table'[Year]*10 & 'Table'[Week Number],'Table'[Year]&'Table'[Week Number])

Week Rank = RANKX(all('Table'),'Table'[Week Start date],,ASC,Dense)

picture for reference,

I want to calculate the sum of the count column for current week and for previous week for each network and office,

So i wrote the daxes,

WTD_Sum = CALCULATE(sum('Table'[Count]), FILTER(ALL('Table'),'Table'[Week Rank]=max('Table'[Week Rank])))

prevWeek_Sum = CALCULATE(sum('Table'[Count]), FILTER(ALL('Table'),'Table'[Week Rank]=max('Table'[Week Rank])-1))

when i used these daxes in visualization, it didnt split according to the network name instead it showed the total count for both the networks,

but the expected output needed is,

NETWORK NAME	WTD_SUM	PREVWEEK_SUM
N1	11	20
N2	11	15

Please help me achieve this! Similarly it should work for offices as well.

@Ashish_Mathur  

@amitchandak 

Thanks,

Dharani

[Ashish_Mathur]

Hi,

Create a Calendar Table and show write those calculated column formulas in the Calendar Table.  Share the download link of the PBI file.

[Mahesh0016]

Hello @Anonymous  everythings is ohk in your logic but only change is you should use WEEKNUM instead of rank.
for E.g
Create Column :

WeekNum = WEEKNUM('Table week'[Date],2)
Use in your calculation :

WTD_Sum =

CALCULATE (

    SUM ( 'Table week'[Count] ),

    FILTER (

        ALL ( 'Table week' ),

        'Table week'[WeekNum] = MAX ( 'Table week'[WeekNum] )

    )

)

############################

prevWeek_Sum =

CALCULATE (

    SUM ( 'Table week'[Count] ),

    FILTER (

        ALL ( 'Table week' ),

        'Table week'[WeekNum]

            = MAX ( 'Table week'[WeekNum] ) - 1

    )

)

@Anonymous I hope this helps you!THANK YOU!!

[Anonymous]

Hi Mahesh, Still the output is same. its gives the same old output. It is not spliting according to the network

[Mahesh0016]

@Anonymous I hope this helps you!THANK YOU!!

Create Column :
WTD_Sum =
CALCULATE (
SUM ( 'Table week'[Count] ),
ALLEXCEPT ( 'Table week', 'Table week'[Network Name], 'Table week'[WeekNum] ),
'Table week'[WeekNum] = MAX ( 'Table week'[WeekNum] )
)

###################################################
prevWeek_Sum =
CALCULATE (
SUM ( 'Table week'[Count] ),
ALLEXCEPT ( 'Table week', 'Table week'[Network Name] ),
'Table week'[WeekNum]
= MAX ( 'Table week'[WeekNum] ) - 1
)

**********************************************************************

Create Measure :

WTD_Sum =

CALCULATE (

    SUM ( 'Table week'[Count] ),

    FILTER (

        'Table week' ,

        'Table week'[WeekNum] = MAX ( 'Table week'[WeekNum] )

    )

)

############################

prevWeek_Sum =

CALCULATE (

    SUM ( 'Table week'[Count] ),

    FILTER (

        'Table week' ,

        'Table week'[WeekNum]

            = MAX ( 'Table week'[WeekNum] ) - 1

    )

)