Animated line chart show only one value

[supasith]

Dear friend

I tried to use the "Play Axis" visual , it should display progressive with historical data but it only displays one value at a time.

how can I fix it?

what I need 

[Anonymous]

This works! 

Thank you so so much!

[MFelix · Accepted solution]

Hi @supasith ,

Create a disconnected table with the values of your play axis and then add the following measure to your line chart and use the new table to the Axis:

TotalSales =
IF (
    SELECTEDVALUE ( 'YearPlayAxisTable'[Year] ) >= MAX ( 'Table'[Year] ),
    SUM ( 'Table'[Value] ),
    IF (
        COUNTROWS ( ALL ( 'YearPlayAxisTable'[Year] ) )
            <= COUNTROWS ( ALLSELECTED ( 'YearPlayAxisTable'[Year] ) ),
        SUM ( 'Table'[Value] )
    )
)

If you want to keep the chart with all the year shown enter a measure like this:

FlatLine = 0

For this line set the line size has 0 and the data labels off

Result below and in attach PBIX file:

If you want you can change the flat line by another value to get it more dinamic and don't set the y-axis maximum and minimum.

[Syndicate_Admin]

I made the same formula, but when I give play I don't join the lines

[MFelix]

Hi, 

Can you send a sample file please. 

[Syndicate_Admin]

https://1drv.ms/u/s!AlqXPOXgExv6glAIN7XhDLiGdon7?e=EDHTcO https://1drv.ms/x/s!AlqXPOXgExv6glFOlnydC3k4aMg1?e=2lsQZW

[MFelix]

Hi @Crosales ,

Check the file attach.

[Syndicate_Admin]

Thank you and if you wanted with months and years
Example

2020-January, 2020-February, 2020-March, ...... 2021-January, 2021-February...... 2023-January

[MFelix]

Ano_Mês = format(EJEMPLO[Fecha], "YYYY-MMM")

Sort = FORMAT(EJEMPLO[Fecha], "YYYYMM") 

Use the following syntax to create the YearPlayaxis table

YearPlayAxisTable = FILTER(SUMMARIZE(EJEMPLO, EJEMPLO[Año], EJEMPLO[Mes], "Date", MAX(EJEMPLO[Fecha])), EJEMPLO[Año] <> BLANK())

Change you calculation to:

TotalSales = 
IF (
    SELECTEDVALUE ( YearPlayAxisTable[Date] ) >= MAX ( EJEMPLO[Fecha] ),
    SUM ( EJEMPLO[Ctd.en UM entrada] ),
    IF (
        COUNTROWS ( ALL ( YearPlayAxisTable[Date] ) )
            <= COUNTROWS ( ALLSELECTED ( 'YearPlayAxisTable'[Date] ) ),
        SUM ( EJEMPLO[Ctd.en UM entrada] )
    )
)

Result below:

Hi @Crosales ,

Add two columns to your model Year-Month and Sort

[Syndicate_Admin]

Thanks a lot!

[Anonymous]

Dear MFelix, 

I wonder if I can also do this for my historical data? The difference with my power bi report is that I use category x axis as "categorical data" instead of year. So I use a category of "100 days", "200 days", 300 days", so on. I followed the DAX above - but it seems it did not work because my data is in "categorical" instead of "continuous data". Could you help me with this? 

I attached the power bi file here.  power bi file 

Thank you so much. 

[MFelix]

Hi @Anonymous ,

Has I refered you need to have a disconnected table so in your case just do the following steps:

• Delete the relationship between Try out and Date Category
• Create the following measure:

RunningTotalAxis = 
IF (
    SELECTEDVALUE ( 'Date category'[Category of date] ) >= MAX ( 'Try out'[Category of date] ),
    [Running Total],
    IF (
        COUNTROWS ( ALL ( 'Date category'[Category of date] ) )
            <= COUNTROWS ( ALLSELECTED ( 'Date category'[Category of date]) ),
       [Running Total]
    )
)

• Format you line chart in the following way:
  • Axis: 'Try Out'[Category of Date]
    • Turn On: Show Items without Data
  • Values: [RunningTotalAxis]
• If you want set the maximum Y-Axis value (I did it to 150 but you can use a measure)
• Play axis visual:
  • 'Date category'[Category of date]

On the image below the bottom chart is the one only using the Try out table for comparision and without interactions with the play axis.

[Anonymous]

Dear @MFelix , 

I have another question. So in my file, I have 4 line graphs in one spreadsheet. What I did in power BI for each graph is to filter it per category. But I realised that in the first line graph - the x-axis is missing "500 days" category. This may be because there is no data in that category. Is it possible to still show that category? I would like to show the x axis category as the ones in Digital events (100, 200, 300, 400, 500 and 600). 

Thank you and I appreciate your help. 

Best,

Sherly