topic Counting with time table and DATESMTD in DAX Commands and Tips

Counting with time table and DATESMTD

JujuT — Mon, 22 Aug 2022 11:49:15 GMT

Hi !

I am working on a database containing different lines, each one referencing to a requests. I am trying to figure out how many request I add for a day or a month...

I first create a "time table" and linked it to my own table and everything looks fine.

So I tried a DAX expression to get the number of issues opened by month:

Nb_isses_opned = CALCULATE(COUNTA('Table FollowUp'[Créé]), DATESMTD('Table Date'[Date]))

And it seems that Nb_issues_opened is empty and I can't figure out why...

Any help ?

Thank you !

Re: Counting with time table and DATESMTD

Greg_Deckler — Mon, 22 Aug 2022 12:43:25 GMT

JujuT You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: Counting with time table and DATESMTD

Anonymous — Tue, 30 Aug 2022 08:26:51 GMT

Hi JujuT ,

Please make sure the related columns 'Table Date'[Date] and 'Table FollowUp' [Date] are in same data format, Date or Datetime. If they are in Datetime please make sure 'Table Date'[Date] contains the time in 'Table FollowUp' [Date].

By default, date table only have time 12:00:00, you need to expand it.

How did you create 'Table Date', by dax or M query? DATESMTD() should be time intelligence function, I suggest you to create date table with continuous date by CALENDAR() or CALENDARAUTO().

Best Regards,
Rico Zhou

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