Forum Discussion

fmigg's avatar
fmigg
New Member
7 years ago

Problem on Data Filter on Table.

Hi,

I'm doing a self-purpose dashboard in which I have to filter expenses from 9 of actual month to 8 of the next month.

This is my formula: 

 

CC- Total a Pagar = IF(DAY(TODAY()) > 8,    
CALCULATE(
        SUM(Gastos[Cantidad]),
        DATESBETWEEN(Gastos[Fecha], DATE(YEAR(TODAY()), MONTH(TODAY()), 9), DATE(YEAR(TODAY()), MONTH(TODAY())+1, 8)),
        Gastos[Forma de Pago] = "CC"    ),    

CALCULATE(
        SUM(Gastos[Cantidad]),
        DATESBETWEEN(Gastos[Fecha], DATE(YEAR(TODAY()), MONTH(TODAY())-1, 9), DATE(YEAR(TODAY()), MONTH(TODAY()), 8)),
        Gastos[Forma de Pago] = "CC"    ))


The dataset started last month, so it has october and november data.  The DAX formula is giving me the expenses correctly,  problem is that it repeats the rows multiple times and associating them with non-expected dates. 

 

When I use the automatic hierachy for Gastos[Fecha], it just return the correct number of rows, but that generates four columns and I just want one.

 That's why I decided to turn off the hierachy, but the three row values gets repeated with different dates. As you can see, the total is right, but I don't know why all these rows are added.

 

I checked on the dataset and these extra rows does not exists. 

I don't understand why is this happening. 

 

Regards,

Need Help
Show and Tell

4 Replies

  • affan's avatar
    affan
    Solution Sage
    7 years ago

    Hi fmigg

     

    I belive you can use the hierarchy and remove the Year, Quarter and Day

     

     

    If this helped you, please mark this post as an accepted solution and like to give KUDOS .

     

    Regards,

    Affan

     

     

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, fmigg

    After my test, It is because that you drag field date into visual and it could be aggregated,

    so it will display multi-row data.

    You may try this way

    add a measure

    type = 
IF (
    DAY ( TODAY () ) > 8,
    "from 9 of actual month to 8 of the next month",
    "from 8 of last month to 8 of the actual  month"
)

    then drag into visual instead of the date field.

     

    Best Regards,
    Lin

     

  • themistoklis's avatar
    themistoklis
    Community Champion
    7 years ago

    fmigg

     

    The tables that you create are aggregated tables.

     

    They aggregate the measures based on the dimensions you specify.

    On the first table if you want to see 1 row only then you have to remove the dimensions 'Day', 'Tipo de Custo'and 'Detalles Luggar' from the table itself.