Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

WEEK CALCULATIONS

Hi,

 

I have a table like below,

 

DateNetwork NameOffice NameCount
7-JunN1OFF 14
7-JunN2OFF 33
6-JunN1OFF 25
6-JunN2OFF 45
5-JunN1OFF 12
5-JunN2OFF 33
4-JunN1OFF 21
4-JunN2OFF 41
3-JunN1OFF 14
3-JunN2OFF 31
2-JunN1OFF 24
2-JunN2OFF 42
1-JunN1OFF 13
1-JunN2OFF 35
31-MayN1OFF 21
31-MayN2OFF 42
30-MayN1OFF 15
30-MayN2OFF 32
29-MayN1OFF 22
29-MayN2OFF 42
28-MayN1OFF 13
28-MayN2OFF 32

 

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 NAMEWTD_SUMPREVWEEK_SUM
N11120
N21115
 
Please help me achieve this! Similarly it should work for offices as well.
 
 
Thanks,
Dharani
Need Help

4 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • Mahesh0016's avatar
        Mahesh0016
        Icon for Super User rankSuper User
        3 years ago
        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
            )
        )


  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

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