Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]) ) )
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] ) ) )
If you mean "cumulative" grouped by different category
create a measure:
cml_by_item = CALCULATE(SUM(Sheet1[kw]),FILTER(Sheet1,[meet dateperiond]=1))
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |