cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
andyhere
Frequent Visitor

remove date from context then do cumulative

Hi,
 
I want to write a measure that removes the current Date context, but retains the rest of the context in order to get current capacity.
My original data is 1 row per item, with dates for when the capacity entered the system and dates for when it left.
I need to be able to filter by other items with this measure, such a technology type, region etc. So it's important that it removes the Date context only. I have a working example with a hard coded context.
 
(The logic is the same as using a trade book to create a balance sheet)
 

Working "hard coded context" method - to continue using this I would need a different measure for every aggregation / visual I want to do

 

Cummulative_by_tech = 
CALCULATE(
    SUM('Capacities'[Capacity [kW]]]),
    FILTER(ALLEXCEPT('Capacities','Capacities'[Technology_type]),
        AND(
            'Capacities'[In-Date] <= MAX('Dates'[Date]),
            'Capacities'[Out-Date] >= MAX('Dates'[Date])
        )
    )
)

 

 

Attempted "dynamic date removal" method

Current capacity = 
CALCULATE(
    SUM('Capacities'[Capacity [kW]]]),
    FILTER(ALLSELECTED('Dates'),
            'Capacities'[In-date] <= MAX('Dates'[Date])
            && 'Capacities'[Exit-date] >= MAX('Dates'[Date])
    )
)
 
Any help very appreciated!
 
 
 
 
3 REPLIES 3
Community Support
Community Support

Re: remove date from context then do cumulative

Hi @andyhere

create a date table which is not connected with your data table.

Create measures in your data table

max = MAX('calendar'[Date])

min = MIN('calendar'[Date])

meet dateperiond = IF(MAX(Sheet1[in_date])<=[min]&&MAX(Sheet1[out_date])>=[max],1,0)

If you mean "cumulative" by date( eg, cumulative sales from 2018/1/1 to 2018/10/1)

write this measure:

cml_asc_date =
CALCULATE (
    SUM ( Sheet1[kw] ),
    FILTER (
        ALLSELECTED ( Sheet1 ),
        [meet dateperiond] = 1
            && [in_date] <= MAX ( [in_date] )
    )
)

8.png

 

If you mean "cumulative" grouped by different category

create a measure:

cml_by_item = CALCULATE(SUM(Sheet1[kw]),FILTER(Sheet1,[meet dateperiond]=1))

9.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

andyhere
Frequent Visitor

Re: remove date from context then do cumulative

Hi Maggie,

 

Thanks for your detailed response!

 

Why does the date/calendar table need to have no relationship to the data table? I would like to retain a relationship there for other graphs.

 

I tried making the first measure you suggested, but using ALLSELECTED I am unable to access the dates columns inside the filter, i guess because there is a relationship set up?

 

thanks,

 

Andy

 

Community Support
Community Support

Re: remove date from context then do cumulative

Hi @andyhere

Please keep the calendar date table( i referred in my previous post) unconnected to your data table so that your problem here can be sloved.

 

To use date from a date table for other visuals, you could create another calendar date table and create relationships among this new calendar date table with your data tables.

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors