The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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.
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.
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 |
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.
Your solution has worked perfectly for me ... Thank you very much
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
I hope I have explained myself, if not, tell me please
Thank you.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
36 | |
24 | |
23 | |
21 | |
16 |