Forum Discussion

LuisLOCapelari's avatar
LuisLOCapelari
Icon for Helper I rankHelper I
3 years ago

Summarize filtering by date

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 

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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

    • LuisLOCapelari's avatar
      LuisLOCapelari
      Icon for Helper I rankHelper I
      3 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

    • LuisLOCapelari's avatar
      LuisLOCapelari
      Icon for Helper I rankHelper I
      3 years ago

      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.