Forum Discussion

iangilsenan's avatar
iangilsenan
Frequent Visitor
6 years ago
Solved

Count values cumulatively over time

We are using a few Power BI Reports in our high schools and are looking for help with counting values cumulatively over time in a Matrix table in a Power BI report. I’ve provided simplified tables b...
Need Help
  • iangilsenan's avatar
    iangilsenan
    6 years ago

    Thanks amitchandak.

    I managed to solve it without creating a Dates table and using the following measure:

     

    Cumulative Present =
    CALCULATE (
    COUNTAX ( FILTER ( ATTENDANCE,[Category] = "Present" ), [Category] ),
    FILTER (
    ALL ( ATTENDANCE[MarkDate] ),
    ATTENDANCE[MarkDate] <= MAX ( ATTENDANCE[MarkDate] )
    )
    )
amitchandak's avatar
amitchandak
Super User
6 years ago

iangilsenan , Not very clear. Typically this how we get cumulative with date calendar

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

 

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

iangilsenan's avatar
iangilsenan
Frequent Visitor
6 years ago

Thanks amitchandak.

I managed to solve it without creating a Dates table and using the following measure:

 

Cumulative Present =
CALCULATE (
COUNTAX ( FILTER ( ATTENDANCE,[Category] = "Present" ), [Category] ),
FILTER (
ALL ( ATTENDANCE[MarkDate] ),
ATTENDANCE[MarkDate] <= MAX ( ATTENDANCE[MarkDate] )
)
)
  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi iangilsenan ,

    Thanks for your kindly sharing your solution and glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.


    Best Regards,
    Yingjie Li