cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdugre Frequent Visitor
Frequent Visitor

Time series - Calculate running total for each blank date and dimension

Hi,

 

I have a similar problem to that thread: https://community.powerbi.com/t5/Desktop/Fill-in-blank-values-in-time-series-data-based-on-last-valu...

 

My data:
1.png

 

 

 

 

 

 

 

 

 

When I pivot:

2.png

 

 

 

 

 

 

To fill the blanks, I can use this measure (thanks to @ImkeF):

Last Funnel status = 
CALCULATE( MAX('Leads Funnel'[FunnelStatusID] )
            ,LASTNONBLANK( DATESBETWEEN( 'Dates'[Date],BLANK(),LASTDATE( 'Dates'[Date] ) )
                           ,CALCULATE(COUNT('Leads Funnel'[FunnelStatusID]))
                          )
          )


3.png

 

 

 

 

 

QUESTION: how can I can get Number of PersonID for each StatusID and each Date, by filling the blank StatusID?


4.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
jdugre Frequent Visitor
Frequent Visitor

Re: Time series - Calculate running total for each blank date and dimension

Ok, I found the solution. I needed to add a calculated column EndDate for each Person and Status. Then, I can calculate number of PersonID for each StatusID at each Date. I need to disable relation between Dates table and my Fact Table for it to work.

 

Number Persons = CALCULATE (
    DISTINCTCOUNT ('Persons status'[PersonID])
    , FILTER('Persons status', 'Persons status'[Date] <= MAX('Dates wihtout relation'[Date]) && 'Persons status'[EndDate] >= MAX('Dates wihtout relation'[Date]))
    )

1.png

 

 

 

 

 

 

 

 

 

2.png

 

 

 

 

 

 

 

 

 

 

Anybody knows another way to do this with an active relation between Dates table and Fact table? 

 

PBIX file https://1drv.ms/u/s!AsMpLDRkiLDdqU_QNs1VV-4aMYno

View solution in original post

9 REPLIES 9
jdugre Frequent Visitor
Frequent Visitor

Re: Time series - Calculate running total for each blank date and dimension

I know I could cross join tables Persons and Status to simplify the calculation but that would give 2 billions+ rows whereas I have only 2 millions rows right now.  

Super User
Super User

Re: Time series - Calculate running total for each blank date and dimension

Not exac'tly sure what you're after, as the samples you've showed don't look consistent to me, but please try this measure:

 

Measure = CALCULATE( COUNT(YourTable[PersonID] )
            ;LASTNONBLANK( DATESBETWEEN( 'Dates'[Date];BLANK();LASTDATE( 'Dates'[Date] ) )
                           ;CALCULATE(COUNT(YourTable[PersonID]))
                          )
          )

And don't forget that your Dates-table has to be connected to YourTable.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




jdugre Frequent Visitor
Frequent Visitor

Re: Time series - Calculate running total for each blank date and dimension

Hi @ImkeF, thanks for the reply. But your measure doesn't do the trick. Here is the pbix file with the expected result: https://1drv.ms/u/s!AsMpLDRkiLDdqU_QNs1VV-4aMYno

 

Thnaks for your help!

 

 

Super User
Super User

Re: Time series - Calculate running total for each blank date and dimension

Sorry, but I don't get your logic here: How can there be 0 in between?

At least my measure is supposed to always return the latest value, even if there are blanks. 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




jdugre Frequent Visitor
Frequent Visitor

Re: Time series - Calculate running total for each blank date and dimension

At each date, I want to count number of Persons with their latest status at this date. For example, on 2018-09-07, no PersonID has a row in the source table. For their calculated latest StatusID, we have on September 7:

 

PersonID 1254 | StatusID 20 (last date with status id 20 is September 2)

PersonID 3325| StatusID 20 (last date with status id 20 is September 6)

PersonID 15487| StatusID 30 (last date with status id 30 is September 4)

 

The measure I'm looking for would calculate for September 7, 0 Persons with StatusID 10, 2 Persons with StatusID 20 and 1 PersonID with StatusID 30.

 

More clear?

Super User
Super User

Re: Time series - Calculate running total for each blank date and dimension

No, unfortunately not.

Hope someone else picks this up.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
jdugre Frequent Visitor
Frequent Visitor

Re: Time series - Calculate running total for each blank date and dimension

Ok, I found the solution. I needed to add a calculated column EndDate for each Person and Status. Then, I can calculate number of PersonID for each StatusID at each Date. I need to disable relation between Dates table and my Fact Table for it to work.

 

Number Persons = CALCULATE (
    DISTINCTCOUNT ('Persons status'[PersonID])
    , FILTER('Persons status', 'Persons status'[Date] <= MAX('Dates wihtout relation'[Date]) && 'Persons status'[EndDate] >= MAX('Dates wihtout relation'[Date]))
    )

1.png

 

 

 

 

 

 

 

 

 

2.png

 

 

 

 

 

 

 

 

 

 

Anybody knows another way to do this with an active relation between Dates table and Fact table? 

 

PBIX file https://1drv.ms/u/s!AsMpLDRkiLDdqU_QNs1VV-4aMYno

View solution in original post

Super User
Super User

Re: Time series - Calculate running total for each blank date and dimension

Now this looks like an "Event with duration"-problem. You might find a solution here: https://www.thebiccountant.com/2016/12/01/analyzing-events-with-a-duration-in-dax-further-simplifica...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




jdugre Frequent Visitor
Frequent Visitor

Re: Time series - Calculate running total for each blank date and dimension

Yes, exactly my problem, thanks! I've read the SQLBI article, these guys are awesome

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 5 members 3,133 guests
Please welcome our newest community members: