CREATE CALENDAR TABLE WITH PARAMETERS based on slicer

[mhendel]

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 : 

MonthTable =

var FullCalendar = ADDCOLUMNS(CALENDAR('public costs test'[Min_Value1];'public costs test'[Max_Value1]);"Début de mois";FORMAT(DATEVALUE( "1/" & MONTH([Date]) & "/" & YEAR([Date]));"d/MM/yyyy"))

return

SUMMARIZE(FullCalendar;[Début de mois])

Min_Value1 = CALCULATE(MIN('public costs test'[Monthly Date]))

Max_Value1 = MAX('public costs test'[Monthly Date])

When i check the two measures, everything is ok : Min_Value1 et Max_Value1 are corrects for the filtered project.

When i check 'MonthTable, it gives me a calendar by month, BUT : the min date is the min date for ALL the projects, and the max date is the max for ALL the projects too.  So the calendar begin at min date in the [Monthly Date] column, the same for the max.

Could you explain me why?  It mades me mad!!!

Or if you have another method which is running, i would be glad to know it!

Regard,

Michael

[Greg_Deckler]

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

[mhendel]

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:

Coûts cumulés = 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]

    )

)   )

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

[Greg_Deckler]

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)

[mhendel]

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

[v-gizhi-msft · Accepted 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

[mhendel]

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

[mhendel]

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!

[mhendel]

Hello,

Any help?  I really need to move on, i'm completely stucked...

Good day,

Michael