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.
Hi,
i have a table 'COSTS' containing : [Project Code],[Monthly Date],[Costs]
For each projects, we have costs at a date.
This table is filtered for a [Project Code] by a slicer.
I want to create a calendar table by month, for the [Project Code], with :
Begin of Calendar = Min Date for the Project in the table 'COSTS'
End of the Calendar = Max Date for the Project in the table 'COSTS'
I wrote this :
Solved! Go to Solution.
Hi,
According to your description, i create a sample to test.
Please take following steps:
1)Create a calendar table:
Calendar = FILTER(CALENDAR(DATE(2015,1,1),DATE(2020,12,1)),DAY([Date])=1)
2)Create relationship:
3)Try this measure:
Measure =
CALCULATE (
SUM ( 'Table'[Monthly Costs] ),
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
4)When select Project Code in slicer, the result shows:
See my attached pbix file.
Best Regards,
Giotto
You cannot have a dynamic table based upon user input within slicers. Tables are calculated at the time of data load and are thus not dynamic like measures. You would need to implement your calendar table within a measure as a VAR and use it within that measure.
Not sure what you are trying to accomplish so hard to provide much more advice. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hello @Greg_Deckler ,
Ok maybe i was not clear...English is not my mother tongue.
I have a table 'public costs test' containing 3 fields : [Code de projet],[Coûts mensuels][Monthly Date] (in English [Project Code],[Monthly costs],[Monthly Date]). This table is filter by a slicer on [Code de projet]
I made a measure to calculate the cumulated costs by month:
And i get the visual you can see on the right of the picture.
My problem is the following : sometimes, there's no costs for a month. So i need to introduce a cost of 0 for that month, to keep the line horizontal for that date.
I don't know if it is more clear now :-).
Cheers,
Michael
Well, you could implement a check at the end of your measure where if the value that is to be returned ISBLANK, then return 0?
So something like:
Coûts cumulés =
VAR __Value CALCULATE(
SUM('public costs test'[Coûts mensuels]);
FILTER(
ALL('public costs test');
'public costs test'[Monthly Date] <= MAX('public costs test'[Monthly Date]) &&
'public costs test'[Code de projet]=SELECTEDVALUE('public contract'[Code de projet]
)
) )
RETURN
IF(ISBLANK(__Value),0,__Value)
Thanks again for your interest and you quick answer. I think i am absolutely not clear :-).
I put a excel example, it may be more understable.
At left, you have my data. As you can see, there's no data for march. What i want is if there's no data for one month, the cumulated data for that month is the same than the last month. That's what i have at right, and that's what i want to have in Power BI. I think the difficulty is made by the slicer and because there's more than one project in my table.
What do you think about this?
Regards,
Mike
Hi,
According to your description, i create a sample to test.
Please take following steps:
1)Create a calendar table:
Calendar = FILTER(CALENDAR(DATE(2015,1,1),DATE(2020,12,1)),DAY([Date])=1)
2)Create relationship:
3)Try this measure:
Measure =
CALCULATE (
SUM ( 'Table'[Monthly Costs] ),
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
4)When select Project Code in slicer, the result shows:
See my attached pbix file.
Best Regards,
Giotto
Hi @v-gizhi-msft ,
I have one more question if you dont mind. With your method, i obtain this :
Is it possible that the max of the date will be the last one where we have monthly cost?
Regards,
Mike
Hi @v-gizhi-msft , thanks a thousand times!!! It's really impressive :-).
@Greg_Deckler : i just bought your book ;-).
Good day to you two and thank you again!
Hello,
Any help? I really need to move on, i'm completely stucked...
Good day,
Michael
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |