Forum Discussion

mikaro's avatar
mikaro
Frequent Visitor
9 years ago
Solved

Monthly inventory

My fact table consists of transactions where every row is either a negative or positive transaction ( i.e. profit or loss) and other info related to them.   I'm trying to figure out what is c...
Need Help
  • tringuyenminh92's avatar
    tringuyenminh92
    9 years ago

    Hi mikaro,

     

    If you are finding cummulative value, it should be:

     

    cumulative qty = CALCULATE(
    SUM(
        Transactions[value]
        ),
        filter(
            ALL( Dates[Date]),Dates[Date] <=MAX(Dates[Date]) && year(Dates[Date])= year(MAX(Dates[Date]))
                ))

    and mapping relationship when using dates table

     

     

    if it's not your expectation, hope you describe your expectation resuslt again so i could quickly figure out solution.

tringuyenminh92's avatar
tringuyenminh92
Memorable Member
9 years ago

Hi mikaro,

 

The date field in slicer is from Dates table or from your fact table? Did you create calculated measure or column?

mikaro's avatar
mikaro
Frequent Visitor
9 years ago

Hi tringuyenminh92!

 

Date field in slicer is from Dates table and all functions in my previous post are calculated measures. For instance if i pick 31.3.2016 from slicer my calculated measure "choice" equals to 31.3.2016. The only calculated column in my project atm is season that is located in the Dates table (31.3.2016 -> 03-16).

 

Thanks in advance for helping me out!

  • tringuyenminh92's avatar
    tringuyenminh92
    Memorable Member
    9 years ago

    Hi mikaro,

     

    Maybe there are something wrong in filter

                Dates[Date]);Dates[Date] <= DATE(YEAR([choice];MONTH([choice];DAY([choice])

     Could you please share pbix and sample data so i could analyze it? And i'm not sure that i understand your expectation in choosing specific date. Could you explain it again?

    • mikaro's avatar
      mikaro
      Frequent Visitor
      9 years ago

      Hi tringuyenminh92,

       

      Here are the files ppix and transactions.

       

      I added some examples and text boxes that will hopefully explain and clarify this problem even more. If they dont help please let me know and i'll try to explain again.

       

      Thanks!

      • tringuyenminh92's avatar
        tringuyenminh92
        Memorable Member
        9 years ago

        Hi mikaro,

         

        If you are finding cummulative value, it should be:

         

        cumulative qty = CALCULATE(
    SUM(
        Transactions[value]
        ),
        filter(
            ALL( Dates[Date]),Dates[Date] <=MAX(Dates[Date]) && year(Dates[Date])= year(MAX(Dates[Date]))
                ))

        and mapping relationship when using dates table

         

         

        if it's not your expectation, hope you describe your expectation resuslt again so i could quickly figure out solution.