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
hoyle23
Regular Visitor

Counting results back x amount of days from a variable column date

Hello,

 

I am trying to recreate the following table that I have completed in excel in PowerBI now.

 

 12 Month Due12 Month CompletePortfolio %
2015 - 62846276597.2%
2015 - 72791273397.9%
2015 - 82696261897.1%
2015 - 925832518

97.5%

 

 

The 12 Month Due and 12 Month Complete are looking at all the results from 12 months back of the date in the first column, I am unable to find a way of totalling a column this way in Power BI.

 

This is my formula in excel: =COUNTIFS('GAS Data'!$I:$I,">="&'GAS Data'!$Z8,'GAS Data'!$I:$I,"<"&'GAS Data'!$Z20)

 

or in simplified terms: =COUNTIFS(Dates,">=01/03/2015",Dates,"<01/04/2016")

 

I feel this shouldn't be complicated but I can only seem to be able to return the count of results from the month in the first column but not any of the 11 months previous to that date.

 

This is the results I am getting in Power BI:

 

PowerBI table.png

 

Any help would be greatly appreciated.

 

Thanks,

4 REPLIES 4
v-haibl-msft
Employee
Employee

@hoyle23

 

Please take a look at this simple sample. I assume we have a dataset like below (have relationship with another calendar table), and we want to count the days from 12 months back. 

Counting results back x amount of days from a variable column date_1.jpg

 

A measure with following DAX formula should work.

I’ve also upload my testing .pbix file here. Please let me know if it is not the result you wanted.

Count = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    DATESBETWEEN (
        'Calendar'[Date],
        FIRSTDATE ( DATEADD ( 'Calendar'[Date], -11, MONTH ) ),
        LASTDATE ( 'Calendar'[Date] )
    )
)

Counting results back x amount of days from a variable column date_2.jpg

 

Best Regards,

Herbert

Thanks for getting back herbert.

 

This isn't quite working for me (could well be me not understaing something), on each day there can be multiple results as shown below:

 

targ_comp_dtcompleted_dt
27/01/1025/01/10
27/01/1027/01/10
29/01/1029/01/10
29/01/1026/01/10
31/01/1023/01/10
02/02/1015/02/10
02/02/1001/02/10
02/02/1011/02/10
02/02/1027/01/10
03/02/1023/02/10
03/02/1019/02/10

 

 

I am able to count the number of results for each month but not the previous months still.

 

For example say there is this amount of results each month:

 

Jan15 - 248

Feb15 - 323

Mar15 - 198

Apr15 - 211

May15 - 133

Jun15 - 344

 

If I wanted the previous 3 months and my month column was June 2015 I would want to count the results in each of these columns from Apr15 - Jun15 so I would get 688 results, likewise for May15 I would want the results from Mar15 - May15 so I would get 542.

 

I feel I should be able to work this out from your formula but I can't seem to get it right.

 

Would I potentially have to create another table with the total results for each day/month in order to achieve this?

 

Any ideas?

 

 

Thanks, 

Andrew.

@hoyle23

 

The formula I provided before should also can be used when there are multiple results on each day. Did you create a calendar table and relate to your table with Date key?

Count = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    DATESBETWEEN (
        'Calendar'[Date],
        FIRSTDATE ( DATEADD ( 'Calendar'[Date], -2, MONTH ) ),
        LASTDATE ( 'Calendar'[Date] )
    )
)

 Counting results back x amount of days from a variable column date_1.jpg

 

Best Regards,

Herbert

Hello Herbert,

 

Thanks for getting back again, I'll have another go at this other the next couple of days and let you know how I get along. I didn't create a relationship between the tables before which is where I am guessing I went wrong(Only been using PowerBI for a couple of weeks so still getting used to it).

 

Thanks for speedy response.

Andrew.

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.