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

[Anonymous]

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?

[amitchandak]

@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.

[Anonymous]

https://drive.google.com/file/d/1LO_EqEs2UhHhjadCw3uLHy_w8OXTHLLE/view?usp=sharing

here is the data and pivots showing the disred outputs on charts depending on slicers (you need to download for pivots)

[v-kkf-msft]

Hi @Anonymous ,

Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

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

[v-kkf-msft]

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.