topic Re: Calculate number from today to end of year in DAX Commands and Tips

Calculate number from today to end of year

Jhadur — Mon, 10 Jun 2024 17:35:33 GMT

Hi,

I am new to Power BI. I need to calculate the number of business cases remaining from "today" to the end of the fiscal year, Oct. 31st.

BC Gate Status is the table with BCDC MONTH holding the dates of the business cases and INITIATIVE NAME being the name of the business case.

CA PPM REFERENCE # INITIATIVE NAME BCDC MONTH IDE-00012580 BC 1 March, 2024 IDE-00010932 BC 2 April, 2024 IDE-00014835 BC 3 May, 2024 IDE-00014841 BC 4 June, 2024 IDE-00014820 BC 5 June, 2024 IDE-00012729 BC 6 June, 2024 IDE-00014243 BC 7 July, 2024 IDE-00011566 BC 8 July, 2024 IDE-00014788 BC 9 July, 2024 IDE-00014792 BC 10 July, 2024 IDE-00014802 BC 11 October, 2024 IDE-00014826 BC 12 October, 2024 IDE-00012712 BC 13 October, 2024 IDE-00014791 BC 14 October, 2024 IDE-00014837 BC 15 November, 2024

Re: Calculate number from today to end of year

Greg_Deckler — Mon, 10 Jun 2024 18:42:39 GMT

Jhadur Is BDC Month a date or text?

Re: Calculate number from today to end of year

Jhadur — Mon, 10 Jun 2024 18:58:24 GMT

It is a date.

Re: Calculate number from today to end of year

Greg_Deckler — Mon, 10 Jun 2024 19:05:07 GMT

Jhadur OK:

Measure = VAR __MinDate = TODAY() VAR __MaxDate = DATE( YEAR( __MinDate ), 10, 31 ) VAR __Table = FILTER( 'BC Gate Status', [BCDC MONTH] >= __MinDate && [BDC MONTH] <= __MaxDate ) VAR __Result = COUNTROWS( __Table ) RETURN __Result

Re: Calculate number from today to end of year

Jhadur — Mon, 10 Jun 2024 20:01:05 GMT

Thanks Greg! I am getting this error with the measure. Any idea how I can fix that?

Re: Calculate number from today to end of year

sevenhills — Mon, 10 Jun 2024 21:48:23 GMT

There are two things, based on the post,

I believe you have to paste only once and you have shared 2 images.

Check what Greg_Deckler pasted the code

it should be somewhat like:

Re: Calculate number from today to end of year

Jhadur — Thu, 13 Jun 2024 14:07:38 GMT

sevenhills Greg_Deckler

I did only paste once in Power BI. I pasted 2 images here to show both of the issues I am having. First is that Power BI won't recognize the 2nd [BCDC MONTH] in the formula. I get an unexpected parameter and cannot find name. The second image shows the error in yellow that I get on the whole formula.

Re: Calculate number from today to end of year

Greg_Deckler — Thu, 13 Jun 2024 19:00:46 GMT

Jhadur Where the red squigly line begins, remove the ", ". Well, should be able to remove the "'BC Gate Status', " entirely. Just the [BDCD MONTH] <= __MaxDate after the &&