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

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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.  

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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!

 

 

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. 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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?

No, unfortunately not.

Hope someone else picks this up.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

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...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

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.