Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Jonathas
Frequent Visitor

To stop the amount stock by

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):

1.PNG

But, when i use the (month/year) the chart don't stop:

2.PNG

can someone help me ?

8 REPLIES 8
v-juanli-msft
Community Support
Community Support

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

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
on-hand by hyerarcal.PNG

Whe i use the normal date the mensure stay correct

on-hand by value.PNG

v-juanli-msft
Community Support
Community Support

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
Community Support
Community Support

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
 

Hi @v-juanli-msft  firtly i'de like to thank by reply

Capturar.PNG

I'm using the mes ano in the X-axis field, i created with the option New group, 
Capturar 1.PNG

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.

Capture18.JPG

Then you can click on the icons to expand all level or axpand from year level to month level.

Capture20.JPGCapture21.JPG

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
Super User
Super User

In visual level filter add the date and use the advanced filter or relative filter.

 

Screenshot 2019-10-11 23.24.39.png

 

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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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.

 

Screenshot 2019-10-11 23.24.39.png

 

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.


 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.