Forum Discussion

ken22g's avatar
ken22g
Frequent Visitor
2 years ago
Solved

Dynamic secondary calendar

Good day everyone, I hope you can help me with my problem,

So I have a main calendar for users who wish to adjust the date range but I have one visual that I do not want to follow that calendar slicer, however, I want it to at least follow the max date set on the calendar slicer. this is to show a cumulative result from the start-up until where the user wants, but since this report is monthly the slicer cannot be set from the start. I tried various solutions one of which is to use a secondary calendar that has no relationship with the main calendar basically the 2nd calendar and the data for that specific visual is isolated from the main. and I have a measure that calculates the value set by the slicer calendar and use that value as the max calendar date for my 2nd calendar. this to me make sense but no matter what I try the max value of my 2nd calendar doesn't follow the value of the measure. 

 

FakeCalendar = 
VAR MinDate = MIN('Machine Population (Proxy)'[Shipping Date])
VAR MaxDateText = [MaxMainCalendarDateText]
VAR MaxDate = DATEVALUE(MaxDateText)
VAR OriginalMaxDate = [MaxMainCalendarDate]
RETURN
ADDCOLUMNS(
    CALENDAR(
        MinDate, 
        MaxDate
    ),
    "DebugMaxDate", MaxDate,
    "OriginalMaxDate", OriginalMaxDate,
    "MaxMainCalendarDateText", MaxDateText
I even had another measure just to read that calculation and turn to text and use that as the calendar limit but it still doesn't follow on what it would look like in a visual card.

as you can see the measure correctly reads the max value of my slicer but when it is used on the table it change...

 

the value that shows up instead is the same max value I have on my main calendar. 

 

if any of you can help me I would appreciate it so much, or maybe if you could suggest a different method that would achieve the same result 

General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Joe_Barry's avatar
    Joe_Barry
    2 years ago

    HI ken22g 

     

    There are some options availablefor culmatice values and you don't need a 2nd date slicer.

     

    For fixed range time intelligence, you can use YTD, QTD, MTD

    Total YTD = TOTALYTD(SUM(Sales[Amount]), Date[Date])

    You can also have culmulative value without the built ion time intelligence of DAX. More info here https://www.daxpatterns.com/cumulative-total/

     

    Sales Amount RT :=
VAR LastVisibleDate =
    MAX ( 'Date'[Date] )
VAR FirstVisibleDate =
    MIN ( 'Date'[Date] )
VAR LastDateWithSales =
    CALCULATE (
        MAX ( 'Sales'[Order Date] ),
        REMOVEFILTERS ()   -- Use ALL ( Sales ) if REMOVEFILTERS () and ALL () 
                           -- are not available
    )
VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            [Sales Amount],
            'Date'[Date] <= LastVisibleDate 
        )
    )
RETURN
    Result

     

    Hope this helps

    Joe

1 Reply

  • Joe_Barry's avatar
    Joe_Barry
    Icon for Solution Sage rankSolution Sage
    2 years ago

    HI ken22g 

     

    There are some options availablefor culmatice values and you don't need a 2nd date slicer.

     

    For fixed range time intelligence, you can use YTD, QTD, MTD

    Total YTD = TOTALYTD(SUM(Sales[Amount]), Date[Date])

    You can also have culmulative value without the built ion time intelligence of DAX. More info here https://www.daxpatterns.com/cumulative-total/

     

    Sales Amount RT :=
VAR LastVisibleDate =
    MAX ( 'Date'[Date] )
VAR FirstVisibleDate =
    MIN ( 'Date'[Date] )
VAR LastDateWithSales =
    CALCULATE (
        MAX ( 'Sales'[Order Date] ),
        REMOVEFILTERS ()   -- Use ALL ( Sales ) if REMOVEFILTERS () and ALL () 
                           -- are not available
    )
VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            [Sales Amount],
            'Date'[Date] <= LastVisibleDate 
        )
    )
RETURN
    Result

     

    Hope this helps

    Joe