Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative total with legend

Hi,

 

I am new to power bi, i am trying to find out the cumulative total for counts.

I have versions for legend:  e.g. osd 3, osd 4, osd 5

The following measure i used for finding cumulative total.

Cumulative total of different version =
CALCULATE(
COUNTA(dummy_data[version]),
ALL(dummy_data),
FILTER(ALLSELECTED(dummy_data[date_installed]),dummy_data[date_installed]<= MAX(dummy_data[date_installed])),VALUES(dummy_data[version])
)
 
This is what i got using the measure, however it will not show any data if that month do not have the data.
I want it to have constant line instead of dots even if the next month has no data, it should take from previous month.
 
 
 
Any help would be greatly appreciated.
Thank you in advance.
 
Need Help
Show and Tell

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Typically this is how we get a cumulative date with a date table

    Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))

    Legend should create different cumulative lines

    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 :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Anonymous ,

     

    Please modify the measure using dax like pattern below and check if this issue persists:

    Cumulative total of different version =
CALCULATE (
    DISTINCTCOUNT ( dummy_data[version] ),
    FILTER (
        ALLSELECTED ( dummy_data ),
        dummy_data[date_installed] <= MAX ( dummy_data[date_installed] )
    )
)

     

    Community Support Team _ Jimmy Tao

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