Comparar valores actuales con respecto a un rango de fechas

[Anonymous]

Buenas,

Informo de lo que quiero hacer. Tengo una matriz en donde en una columnas se muestra la fecha actual, en filas los clientes y los valores de las celdas es el numero total de trabajadores....Hasta aquí todo bien...Y ahora me pidieron: mostrar los trabajadores por clientes con respecto a la semana anterior, mes anterior, una semana en concreto de hace 6 meses. Para ello llevo guardando un hisotrico de esos ultimos 6 meses pero no soy capaz de representarlo en una matriz.

Si lo hago por meses pefecto porque como Columna pongo mi fecha-->Mes y listo pero no puedo por la semana anterior con respecto a hoy o una semana concreta que me han dicho.

Si además me queréis ayudar a mostrar en porcentaje cuanto es un valor con respecto a ese mismo valor la semana anterior, el mes anterior o la semana que me indican. Quieren conocer si el Cliente XA la semana pasada subio un 4% el nº de trabajadores y respresentarlo en una barra en rojo si es menos y en otro 

Desde ya, muchísimas gracias. 

[v-alq-msft]

Hi, @Anonymous 

Based on your desription, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You may create calculated columns and measures as below.

Calculated column:

YearWeekCal = YEAR([Date])*100+WEEKNUM([Date])

YearWeek = YEAR([Date])*100+WEEKNUM([Date])

Measure:

Today = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
       ALL('Table'),
       [Date]=TODAY()
    )
)

Last Week = 
var _lastweeknum = 
CALCULATE(
    MAX('Calendar'[YearWeek]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[YearWeekCal]<YEAR(TODAY())*100+WEEKNUM(TODAY())
    )
)
return
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[YearWeek]=_lastweeknum
    )
)

Last Month = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Date]>=EOMONTH(TODAY(),-2)+1&&
        'Table'[Date]<=EOMONTH(TODAY(),-1)
    )
)

Custom Weeks = 
CALCULATE(
    SUM('Table'[Value]),
    ALLSELECTED('Table')
)

Today is 9/11/2020. Here is the result:

Best Regards

Allan

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

[Anonymous]

Thank you for answering me. After testing the case that you send me, it works for me but you are calculating the data of all the clients, I need: obtain the value of a field of the model for each of the clients. The date range filter I think with your help, I already have it.

Model:

1	CLIENTE A	15	2020-09-01
2	CLIENTE A	25	2020-09-02
3	CLIENTE A	28	2020-09-03
4	CLIENTE B	58	2020-09-01
5	CLIENTE B	64	2020-09-02
6	CLIENTE B	79	2020-09-03

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

I modified data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

You may modify the measures as below.

Today = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Client],
        "Result",
        CALCULATE(
            SUM('Table'[Value]),
            FILTER(
                ALL('Table'),
                'Table'[Client]=EARLIER('Table'[Client])&&
                'Table'[Date]=TODAY()
            )
        )
    ),
    [Result]
)

Last Week = 
var _lastweeknum = 
CALCULATE(
    MAX('Calendar'[YearWeekCal]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[YearWeekCal]<YEAR(TODAY())*100+WEEKNUM(TODAY())
    )
)
return
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Client],
        "Result",
        CALCULATE(
            SUM('Table'[Value]),
            FILTER(
                ALL('Table'),
                'Table'[Client]=EARLIER('Table'[Client])&&
                'Table'[YearWeek]=_lastweeknum
            )
        )
    ),
    [Result]
)

Last Month = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Client],
        "Result",
        CALCULATE(
            SUM('Table'[Value]),
            FILTER(
                ALL('Table'),
                'Table'[Client]=EARLIER('Table'[Client])&&
                'Table'[Date]>=EOMONTH(TODAY(),-2)+1&&
                'Table'[Date]<=EOMONTH(TODAY(),-1)
            )
        )
    ),
    [Result]
)

Custom Weeks = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Client],
        "Result",
        CALCULATE(
            SUM('Table'[Value]),
            FILTER(
               ALL('Table'),
               'Table'[Client]=EARLIER('Table'[Client])&&
               'Table'[YearWeek] in DISTINCT('Table'[YearWeek])
            )
        )
    ),
    [Result]
)

Result:

Best Regards

Allan

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

[Anonymous]

Your solution has worked perfectly for me ... Thank you very much

[Fowmy]

@Anonymous 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

I would like that instead of getting the clients in rows, in columns the months, and the value is the total number of workers, it would be to compare the total number of today vs last week, today vs last month, today vs custom week

Link: https://grupoarpada-my.sharepoint.com/:i:/g/personal/jaespinosa_grupoarpada_onmicrosoft_com1/EQkWDob...

I hope I have explained myself, if not, tell me please

Thank you.