Forum Discussion

christianfcbmx's avatar
christianfcbmx
Icon for Post Patron rankPost Patron
7 years ago
Solved

Comulative formula by week? is it possible?

Hi! I need to make a comulative formula by week, anybody got an idea how in my example?

I have two main problems:

 

1-. I want to use the column week of the Mineral Ext+ table not the calendar but I cant get a logic result.

2-. I need to hide months there are no data...my formula shows the other months.

Sample data: https://www.dropbox.com/s/guw4ffb5aoxrrp3/PBI%20EXAMPLE.pbix?dl=0

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi christianfcbmx .

    Yes, it is possible. You can do cumulative by step condition filter on year and weeknum.

    For example:

    Cumulative =
VAR currDate =
    MAX ( calendar[Date] )
RETURN
    CALCULATE (
        SUM ( table[Amount] ),
        FILTER (
            ALLSELECTED ( table ),
            YEAR ( table[date] ) = YEAR ( currDate )
                && WEEKNUM ( table[date], 1 ) <= WEEKNUM ( currdate, 1 )
        )
    )

    Notice: please do mask on sensitive date before share and not attach whole data model table.(you only need to share key fields which formula required)

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi christianfcbmx .

    Yes, it is possible. You can do cumulative by step condition filter on year and weeknum.

    For example:

    Cumulative =
VAR currDate =
    MAX ( calendar[Date] )
RETURN
    CALCULATE (
        SUM ( table[Amount] ),
        FILTER (
            ALLSELECTED ( table ),
            YEAR ( table[date] ) = YEAR ( currDate )
                && WEEKNUM ( table[date], 1 ) <= WEEKNUM ( currdate, 1 )
        )
    )

    Notice: please do mask on sensitive date before share and not attach whole data model table.(you only need to share key fields which formula required)

    Regards,

    Xiaoxin Sheng