Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

x axis with single particular dates shown, changing dynamically based on slicer

Hi all,

 

I have a table with EBITDA values assigned to different dates, some are monthly, some are annually, depending how old they are (the most recent ones are monthly). This data is for 5 different entities.

I am looking for a solution in which I could create a chart, where my values would be presented only for:

 

a) first date available in my dates column for each entity, depending which entity is selected on the slicer

b) december values for each year between date a) and date c)

c) date selected on the slicer as the current date i.e. latest date looked at

 

Does anyone knows how to approach this?

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Usually in such cases, I build dates

    example for year

    date = date([year],1,1)

     

    for month

    date = date([year], [month],1)

     

    for mmm- yyyy format

    date = "01-" & [month Year] //change data type to date.

     

    join with date table and analyze 

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi Anonymous 

         

        Put date column into slicer, then select "Before".

        Create measure:

        Measure = 
var MaxDate = EOMONTH(MAXX(ALLSELECTED('source data'),'source data'[date]),0)
var MinDate = EOMONTH(MINX(ALLSELECTED('source data'),'source data'[date]),0)
var Previous = 
    CALCULATE(
        SUM('source data'[value]),
        FILTER(
            'source data',
            'source data'[Year] < YEAR(MaxDate)
            && 'source data'[Month] = 12
            || 'source data'[date] <= MinDate
        )
    )
var CurrentValue =
    CALCULATE(
        SUM('source data'[value]),
        GROUPBY(
            'source data',
            'source data'[Entity ID],
            'source data'[type]
        ),
        'source data'[Year_month] = FORMAT(MaxDate,"yyyy_m")
    )
var result = 
    IF(
        MAX('source data'[Year_month]) = FORMAT(MaxDate,"yyyy_m"),
        CurrentValue,
        Previous
    )
return 
    IF(
        ISFILTERED('source data'[Year]),
        result,
        Previous + CurrentValue
    )

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.