time based calculation; per week (starts from saturday) and per 28 days

[andrea_p]

Hello, I need to make calculation based on weekly period and 4 week period

The week starts every Saturday and ends on Friday and I need to show the calculations of 4 latest weeks

Besides, the 5th to 8th week will be calculated as one period, and so do the 9th to 12th, 13th to 16th, 17th to 20th, and so on

My query is connected to an online source where the date is automatically updated (so there are always new entries everyday).

The output should look like this, I want to use the 'Matrix' Visualizations

can anyone help me with this?

[v-shex-msft]

HI @andrea_p,

I'd like to suggest you add calculated column to split your table with custom date period based on week number, then you can direct category as row, custom date period as column, sum of amount column as value to create matrix visual.

If you confused on coding formula, please share some sample data for test and coding formula.

Regards,

Xiaoxin Sheng

[andrea_p]

Hello @v-shex-msft, thank you for your reply.

hereby I attached the sample of my project.

https://drive.google.com/drive/folders/1cK4o43oUFYtdcX87iORUxjZmvgMmN_zC?usp=sharing

I managed to make the weekly report but I'm stil clueless about the 4week period report.

this is what I managed to do until now.

the first table shows the weekly period calculation, which is already correct

while the one below should be showing a 4 weeks period,

I can't manage to divide the dates to per 4 weeks period and in mine, ones still overlap the others.

whilst what I need is week 1-4 from current date as a single group, 5th to 8th weeks from current period as an another group and so on.

the data is updated automatically, so I need a formula that works for this.

I need to group the week to: for instance,

04-08 to 31-08

01-09t o 28-09

29-09 to 26-10

and so on

And when a new week appears, the group will become:

11-08 to 07-09 

08-09 to 05-10

06-10 to 02-11

and so on

will really appreciate your help

[v-shex-msft · Accepted solution]

Hi @andrea_p,

You can refer to below formula to create a column to store dynamic category range based minimum date:

Dynamic =
VAR _minimumDate =
    MINX ( ALL ( 'Table' ), [Date] )
VAR _weekStart =
    _minimumDate - WEEKDAY ( _minimumDate, 1 )
VAR _currWeekStart =
    [Date] - WEEKDAY ( [Date], 1 )
VAR _start =
    _currWeekStart
        - MOD ( _currWeekStart - _weekStart, 28 )
VAR _end = _start + 27
RETURN
    FORMAT ( _start, "mm/dd/yyyy" ) & " ~ "
        & FORMAT ( _end, "mm/dd/yyyy" )

Regards,

Xiaoxin Sheng

[andrea_p]

hello @v-shex-msft

thank you for answering my question.

the formula works properly, however, it is in 'text' format, and so it can not be used to sort the column in order.

is there any way to show calue in date format?

[v-shex-msft]

Hi @andrea_p,

You can modify my formula to keep start part or end part in formula, current power bi not support analysis date range as date format:

Dynamic END =
VAR _minimumDate =
    MINX ( ALL ( 'Table' ), [Date] )
VAR _weekStart =
    _minimumDate - WEEKDAY ( _minimumDate, 1 )
VAR _currWeekStart =
    [Date] - WEEKDAY ( [Date], 1 )
VAR _start =
    _currWeekStart
        - MOD ( _currWeekStart - _weekStart, 28 )
VAR _end = _start + 27
RETURN
    DATEVALUE ( FORMAT ( _end, "mm/dd/yyyy" ) )

Regards,

Xiaoxin Sheng