To stop the amount stock by

[Jonathas]

Dear, i'm breaking my head with this problem, i need to stop of to calculate the balance stock at today using mouth/year because a have lot of data to use amount by day, in summary, my model is as forward:

f_amount

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 ?

[Jonathas]

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 ?

[v-juanli-msft]

Hi @Jonathas 
Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[Jonathas]

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

[v-juanli-msft]

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.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

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?

Best Regards
Maggie

[Jonathas]

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"

[v-juanli-msft]

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.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

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.

[Jonathas]

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.

---