Problem on Data Filter on Table.

[fmigg]

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,

[themistoklis]

@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.

[v-lili6-msft]

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

[Ashish_Mathur]

Hi,

Could you share your PBI file and show the expected result.

[affan]

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