March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
data_sk Product_sk Amount Inventory_sk
1 1 1119 1
2 1 1120 1
3 1 1110 2
1 1 1111 1
d_inventory
inventory_sk code nome
1 801 main
2 802 store_east
d_data
d_data campo_data ano mes ano
1 01/01/2010 2010 january2010
2 01/01/2010 2010 january2010
d_product
d_product_sk name
1 glove
2 jeans pant
3 purple skirt
To get the amount i'm using this function:
On-Hand Quantity = //Encontra a ultima quantidade do produto, usando o filter para não somar os historicos VAR last_Data = LOOKUPVALUE( 'public d_data'[campo_data]; 'public d_data'[data_sk]; CALCULATE( MAX('public f_saldo_historico'[data_sk]); FILTER( ALL('public d_data'); 'public d_data'[campo_data] <= MAX('public d_data'[campo_data]) && 'public d_data'[campo_data] <= TODAY() ) ) ) VAR lastValue = CALCULATE ( SUM('public f_saldo_historico'[quantidade]); FILTER ( ALL ('public d_data'); 'public d_data'[campo_data] = last_Data && 'public d_data'[campo_data] <= TODAY() ) ) RETURN IF( SELECTEDVALUE('public d_data'[campo_data]) <= TODAY(); lastValue; BLANK() )
When a plot the chart by campo_data the result is correct, the chart stop at today (11/10/2019):
But, when i use the (month/year) the chart don't stop:
can someone help me ?
Hi @v-juanli-msft i tried to put how you told, but the result is the same, the mensure don't stop at today. Look: in portugues Ano = Year, Mes = month
Whe i use the normal date the mensure stay correct
Hi @Jonathas
Your sample tables are differnt from the tables' name in your formula-"public f_saldo_historico".
If you want to show "Cumulative Totals Only Up To Specific Dates(today)", you could check the following link:
https://www.youtube.com/watch?v=JnhXyQ8eyuo
Please try the guide above and my suggestions of using date hierarchy in a column chart.
Hi @Jonathas
Do you use "campo_data" and "ano", " mes ano" columns in the X-axis field of the line chart?
Could you show a screenshot of the columns/measures added into the line chart?
Hi @v-juanli-msft firtly i'de like to thank by reply
I'm using the mes ano in the X-axis field, i created with the option New group,
The field content is : (are at brazilian format day/month/year)
data_sk campo_data ano mes mes_ano
1 02/01/10 2010 1 "janeiro de 2010"
.
.
.
9125 26/12/34 2034 12 "dezembro de 2034"
Hi @Jonathas
First we should know the correct way to use date hierarcy.
Then you can click on the icons to expand all level or axpand from year level to month level.
In visual level filter add the date and use the advanced filter or relative filter.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
Hello @amitchandak i need to filter the value because after i gonna compare with other period
@amitchandak wrote:In visual level filter add the date and use the advanced filter or relative filter.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
86 | |
77 | |
57 | |
52 |
User | Count |
---|---|
201 | |
137 | |
108 | |
73 | |
68 |