cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhendel
Helper III
Helper III

CREATE CALENDAR TABLE WITH PARAMETERS based on slicer

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

 

 

1 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:

1.jpg

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:

6.PNG

See my attached pbix file.

 

Best Regards,

Giotto

 

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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]

 

Annotation 2020-05-10 130652.png

 

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

 

 

 

 

 

 

 

 

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)

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

Capture.PNG

 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:

1.jpg

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:

6.PNG

See my attached pbix file.

 

Best Regards,

Giotto

 

View solution in original post

Hi @v-gizhi-msft ,

 

I have one more question if you dont mind.   With your method, i obtain this : 

 

Capture.PNG

 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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors