Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure to keep count of tasks not completed each month

Hello, 

 

I'm in need of some help. I need to do a visualization that will count the comulitive amount of tasks left behind. 

So for example if I have: 

  createdresolved
 task1 01/01/202202/01/2022
 task202/01/202201/02/2022
 task303/01/202202/02/2022
 task4 04/01/2022 
 task505/02/202206/02/2022
 task603/03/202204/03/2022
 task705/03/2022 
 task808/04/2022 

 

The final result would be: 

 transactopen close
jan041
feb312
mar121
apr210

 

In january no transacted were received since there is no previous data. In february 3 transacted tasks were received (task2, 3 and 4). In march, 1 task was received (task4) since it hasn't been resolved in this month. Finally april received two transitioned tasks (task4 and 7) that are the tasks unsolved from previous months.

 

 

I was able to do the open and close but not the transact (tasks that transacted to the next month). 

At the moment I have a Dimension Table for Dates that connects to the open date, on the usual relationship of one to many to my fact table. I've also connected the resolved. 

 

Hope someone can help me. Thank you. 

 

1 ACCEPTED SOLUTION
olgad
Super User
Super User

Hi, 
i resolved it without taking the calendar/date table may be you can work further with that to adapt to your case,  if you dont succeed, please let me know which relationship you have between the resolved and the date table, inactive? then may be I will take another look.

olgad_0-1675895583266.png

Cumulative Total =
var maxdate=Date(Year(max('Table'[created])), Month(max('Table'[created])), 01)
var resolved=Date(Year(max('Table'[resolved])), Month(max('Table'[resolved])), 01)
RETURN
CALCULATE(
    Count('Table'[task]),
    FILTER(
        All('Table'),
       
       ('Table'[created]<maxdate && 'Table'[resolved]=blank()) ||  'Table'[created]<maxdate && EOMONTH('Table'[resolved],0)=EOMONTH(max('Table'[created]),0)
    ))

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hi, 

Thanks for the help! I feel I'm getting closer. Unfortunately this is still not working. I believe this is due to this part: 

EOMONTH('Table'[resolved],0)=EOMONTH(max('Table'[created]),0)

Could you please explain what you are trying to compare here? I'm new to powerbi. 

This is the results that I'm getting with your measure: 

Screenshot (21).png

As you can see there were 59 tasks that went from february to march, but the measure shows 167... 

Thanks for the help! 

Hi, EOMONTH is just making end of the month out of the date, so i can compare the months 31/1/2023 of the resolved with 31/1/2023 of the created, disregarding the day dates. 
Do you want to ping me your file in private messages? I can take a look.


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
olgad
Super User
Super User

Hi, 
i resolved it without taking the calendar/date table may be you can work further with that to adapt to your case,  if you dont succeed, please let me know which relationship you have between the resolved and the date table, inactive? then may be I will take another look.

olgad_0-1675895583266.png

Cumulative Total =
var maxdate=Date(Year(max('Table'[created])), Month(max('Table'[created])), 01)
var resolved=Date(Year(max('Table'[resolved])), Month(max('Table'[resolved])), 01)
RETURN
CALCULATE(
    Count('Table'[task]),
    FILTER(
        All('Table'),
       
       ('Table'[created]<maxdate && 'Table'[resolved]=blank()) ||  'Table'[created]<maxdate && EOMONTH('Table'[resolved],0)=EOMONTH(max('Table'[created]),0)
    ))

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Here are the steps you can follow:

1. Create calculated column.

vyangliumsft_0-1675836191382.png

Date =
CALENDAR(
    DATE(
        2022,1,1),
        DATE(
            2022,12,31))

2. Create calculated column.

Month =
FORMAT('Date'[Date],"mmm")

vyangliumsft_1-1675836191384.png

3. Create measure.

transact =
COUNTX(
    FILTER(ALL('Table'),
    FORMAT('Table'[created],"mmm")=MAX('Date'[Month])&&'Table'[resolved]<>BLANK()),[task])
open =
COUNTX(
    FILTER(ALL('Table'),
    FORMAT('Table'[created],"mmm")=MAX('Date'[Month])),[task])
close =
COUNTX(
    FILTER(ALL('Table'),
    FORMAT('Table'[created],"mmm")=MAX('Date'[Month])&&'Table'[resolved]=BLANK()),[task])

4. Result:

vyangliumsft_2-1675836191389.png

 

Best Regards,

Liu Yang

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

Anonymous
Not applicable

This didn't work as all transacted spaces appear as blank. But please review my question as I believe I've explained myself wrong. 

JirkaZ
Solution Specialist
Solution Specialist

You could tackle that with various approaches. 
Example - create a measure like so:

Transfered Tasks := 

SUMX('TasksTable', IF(ISBLANK('TasksTable'[resolved]), 1, 0))
Anonymous
Not applicable

The problem with this solution is that it doesn't attend to the creation date of the tasks. So for example, tasks that were created in January and only Closed in february won't show up as a transacted task from january to february because although the resolve date is not blank, they were not resolved in that month.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.