Forum Discussion

zidek22's avatar
zidek22
Frequent Visitor
5 years ago
Solved

Changing column order according to data from table.

Hello friends, I got a request to create the following:

I have a table that has revenue data for each day of each month. Like:

 

[TABLE1]

Day1 Day2 Day3 Day4 etc...

2500 1300 2000 3000 etc...

 

What I need, is to add a summary column after the last day of each week. For example, if Day2 of the month is Sunday, it would look like:

 

[NEWTABLE]

Day1   Day2    Summ    Day3   Day4   etc...

2500   1300     3800      2000   3000   etc...

 

If Day3 is Sunday it would look like this:

 

[NEWTABLE]

Day1   Day2   Day3   Summ   Day4   etc...

2500   1300   2000    5800     3000   etc...

 

I have a table that tells me which week of which month has how many days, like this for this August 2020:

 

[TABLE2]

Month    Week1    Week2    Week3    Week4    Week5    Week6

         8             2             7             7             7             7             1

 

So for August, there would be the Summary column after Day2, Day9, Day16, Day23, Day30, Day31. Is there anything like this possible? Like creating New Table, filtering by month and doing like:

 

IF(TABLE2.Week1 == 2) {NewColumn("Summ") AFTER "TABLE1.Day2" AS TABLE1.Day1 + TABLE2.Day2

 

Please excuse the pseudo code, I don't know the DAX language very well. In this fashion, I would have to write it 7 times (for each Sunday position in the first week), but I am absolutely lost how could I even get started on this.

 

THANK YOU KIND PEOPLE

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi zidek22

     

    I suggest you unpivot your table to following format:

     

    Create a weeknum column in your table:

    WEEKNUM = WEEKNUM('Table'[Date])

     

     

    Then you can create a summary column:

     

    Summary =
VAR a =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[weeknum] = EARLIER ( 'Table'[weeknum] ) )
    )
RETURN
    IF (
        'Table'[Date] = a,
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[weeknum] = EARLIER ( 'Table'[weeknum] ) )
        ),
        BLANK ()
    )

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Keep in mind that internally dates are numbers.

     

    All you need to do is calculate your summaries, and give them a date code of "Sunday plus a bit".  Then you UNION the two tables together (date values and summaries) and sort them by the date code.

     

    Make sure that your users are ok with this display. It can easily lead to confusion and misinterpretation.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi zidek22

     

    I suggest you unpivot your table to following format:

     

    Create a weeknum column in your table:

    WEEKNUM = WEEKNUM('Table'[Date])

     

     

    Then you can create a summary column:

     

    Summary =
VAR a =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[weeknum] = EARLIER ( 'Table'[weeknum] ) )
    )
RETURN
    IF (
        'Table'[Date] = a,
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[weeknum] = EARLIER ( 'Table'[weeknum] ) )
        ),
        BLANK ()
    )

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai