How to get sums of sales every 2 weeks for the past year before the end date specified in slicer?

[iamriz]

Hello everyone, I want to have sums of sales data within 2 weeks for the past year before the end date specified in the date slicer. I have been searching and trying ways but it is not working.

The closest DAX function I found was to use the following as a new Column:

Bi-weekly Code = QUOTIENT(DATEDIFF('T10_Calendar'[Date],[SlicerEndDate],DAY),14)+1

// Where [SlicerEndDate] is a Measure for getting the end date set in the date slicer.

// Where 'T10_Calendar'[Date] is a CALENDAR Date

The problem with the above DAX function is [SlicerEndDate] measure does not get the end date specified in the slicer. Instead, it always gets the max/last date of the Calendar table, making the first past 2 weeks value incorrect if the end date specified is an earlier date than the max date of the Calendar table.

Any hint on how to work around this will be much appreciated.

[v-henryk-mstf · Accepted solution]

Hi @iamriz ,

According to your test file, I did a test and it seems impossible to achieve such a visual. For the created Bi-weekly Code, because it is a calculated column, even if a dynamic date change value is used in the parameter, the calculation still uses a constant value (the maximum value in the Data table). So the above result will not change with the date in the slicer.

Bi-weekly Code = QUOTIENT(DATEDIFF(Orders[Date2],[SlicerEndDate],DAY),14)+1

If you replace the column with a measure, the measure cannot be used as the x-axis, so the above visual cannot be obtained. Hope the above explanation is helpful to you.

Best Regards,
Henry

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

[iamriz]

Thanks, Henry, for checking. The same is my understanding with the other related posts I found. It would have been great if a measure could be used as x-axis values. That would be awesome.

[v-henryk-mstf]

Hi @iamriz ,

You can try the folloing formula:

Bi-weekly Code = 
var select_ = SELECTEDVALUE('Table'[Date])
return
QUOTIENT(DATEDIFF('Table'[Date],select_,DAY),14)+1

If the problem is still not resolved, can you provide more information, such as testing the data model (deleting sensitive information), etc. I will answer for you as soon as possible.

Best Regards,
Henry

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

[iamriz]

Hi Henry, thanks for your response. Really appreciate it. I created a sample test data and illustration in the attached Power BI file. In the example data as attached, the period 7/08/2021 to 7/21/2021 (or TODAY date) are marked with "Bi-weekly Code" = 1 (first 2 week period in the past). What I want to achieve is if I set the end date on the slicer, for example, 7/18/2021, the data marked with "Bi-weekly Code" = 1 will also move to records with period 7/05/2021 to 7/18/2021, and update the line chart accordingly with the plotted counts.
I hope you can check and advise on how to work around this. Thank you very much.

https://drive.google.com/file/d/11Vko9wSsYUmACE45xt_ThLUfhE55E2Hn/view?usp=sharing

[Fowmy]

@iamriz 

Yes, you cannot access a value from the report slicer when you build a calculated column or table. you can use this measure to get the past 2 weeks sales if I understood the requirements correctly.

Past 2 Week Sales = 
var __currentdate = SELECTEDVALUE(Query1[Date]) return
CALCULATE(
    SUM(Query1[Sales]),
    FILTER(
        ALL(Query1[Date]),
        Query1[Date] < __currentdate && Query1[Date] >= __currentdate - 14
    )
)