topic Summarize filtering by date in DAX Commands and Tips

Summarize filtering by date

LuisLOCapelari — Sun, 10 Sep 2023 14:54:05 GMT

Hey guys. I'm Brazilian and I need to do a dax to calculate the amount of ID_GERADOC for each ID_LOGIN in the FATO_GERADOC table, in a given period. This period will be selected in a slicer from the DIM_DATA table.
I've already tried to create a table "Materiais" using Summarize with Filter on the FATO_GERADOC table, filtering by DATA_REGISTRO <= MAX(DIM_DATA[Data]), but it didn't work. Can someone help me?

https://drive.google.com/file/d/1BqapkYRB7DUysPvHFQcM67DqJTvMTtyQ/view?usp=sharing

Re: Summarize filtering by date

amitchandak — Mon, 11 Sep 2023 06:59:20 GMT

LuisLOCapelari , The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

if you looking for a Cumulative total then you can try like, with help from date table

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

Re: Summarize filtering by date

LuisLOCapelari — Tue, 12 Sep 2023 11:49:19 GMT

Hello amitchandak , thanks for responding to me.
This is a scenario of an educational platform where teachers create lists of exercises.
The FATO_GERADOC table has information on all lists that a teacher creates.
I want to take a specific period, selecting the dates from the DIM_DATA table using a slicer, then calculate the number of lists created per teacher. Then I want to take and group this number of lists and have something like: X teachers created Y lists in period P.

Re: Summarize filtering by date

Anonymous — Wed, 13 Sep 2023 05:07:57 GMT

Hi LuisLOCapelari ,

You can try using the following dax:

Measure = var _select=SELECTEDVALUE('DIM_DATA'[Data]) return COUNTX( FILTER(ALL(FATO_GERADOC), 'FATO_GERADOC'[ID_LOGIN]=MAX('FATO_GERADOC'[ID_LOGIN])&&'FATO_GERADOC'[DATA_REGISTRO]<=_select),'FATO_GERADOC'[ID_GERADOC])

Best Regards,

Liu Yang

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

Re: Summarize filtering by date

LuisLOCapelari — Wed, 13 Sep 2023 11:59:57 GMT

Hello Anonymous . This doesn't work. Because there are several date filters, there is no single value to get from SELECTEDVALUES.
The idea is to be able to filter according to year, quarter and month. And dynamically, for the selected period, generate a table calculating the number of lists generated by each teacher. Then, discover the unique values for the number of lists, and for each number of lists, find out how many teachers fit into this group.
I will attach images illustrating it.