Forum Discussion

Fatman121's avatar
Fatman121
Frequent Visitor
4 years ago

Calculate total between matrix headers

Hi!

 

I have matrix. It is looks like this:

 

datedate1date1date2date2
branchSalesCount_of_salesSalesCount_of_sales
branch1Measure1Measure2Measure1Measure2
Branch2Measure1Measure2Measure1Measure2

The Measure1 must calculate total sales between dates in header. The left date border must not be incluaded, the right border must be incluaded.

 

For examle:

If headers looks like 2022-06-22, 2022-06-24

For date 2022-06-24 Measure must calculated sum from 2022-06-22 to 2022-06-24. The boarders - (2022-06-22, 2022-06-24]

 

I can`t hardcode 2 days in measure, because headers depends on user selection, it can by day by day, week by week, some specific dates.

 

My data sample looks like:

 

DateBranchOrder_idSalesRevenue
Date1Branch1110100
Date2Branch1215150
etcetcetcetcetc

 

Any idea how realize that?

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Fatman121 ,

    Please have a try.

    Create a calendar date table first.

    date=calendar(date(2021,1,1),date(2022,12,31))

    Then put the date column into the slicer.

     

    Then create a measure.

    measure=var _selemindate=selectedvalue('table'[date])
var _selemax=selectedvalue('date'[date])
return
calculate(sum('table[value]),filter(all('table'),'table'[order]=selectedvalue('table'[order])&&'table'[date]>_selemindate&&'table'[date]<=_selemax))

     

    If I have misundetstood, please provide more details with your desired output and some sample data.

     

    How to Get Your Question Answered Quickly 

     

    Best Regards

    Community Support Team _ Polly

     

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