Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dax Expression

I have table with date wise data,i want to show the value based on month end. If it have value in jan 31st and dosnot have value in feb 28 then i want to show same value in 28 feb. Please help me t...
Dataflow
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Thanks For the response.

    This solution meets 90%  and only issue was i dont want to sum up Quantity.

    ie expected result shown below 

     

    Month  Count

    Jan        1000

    Feb       1000

    Mar       1000

    Apr        500

    May       500

    Jun        500

     

    The above one is based on the file you shared

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    Which code did you use in this screenshot? I think you didn't build a relatisonship in above sample. I think you want yo show the QTY at the last day instead sum of the QTY of the whole month. Could you tell me why Jan, Feb and Mar show 2000 in your screenshot? Please show me the result you want by screenshot. 

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

collinq's avatar
collinq
Super User
4 years ago

Hi Anonymous ,

 

I think that this request might be better in the developer forum so I have moved it there.  Also, I think a bit more explanation is going to be needed as to what you are truly trying to do - what is the actual thing that you are trying to do, from a logic perspective?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi collinq ,

    Thanks for the response.

    Am trying to figure out sum of customer count in each month.

    Let say in 31 jan 2021 active customer count is  2000,then feb there is no customer addition and customer count in 28 feb is 2000.So here there is no change in count ,i will not get the data from source,because we pulling data that have a change.In this case there is no record for particular operator in Feb but i want show count in visual that is same as january.Again the if the march month also dont have any change,i want show same figure as in january.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous 

      I think you want to create a measure to show rolling total by month.

      Here I build a sample.

      Build a date table by dax.

      Date =
VAR _T =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 05, 31 ) ),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] )
    )
VAR _T1 =
    ADDCOLUMNS (
        _T,
        "Last Date each Month",
            MAXX (
                FILTER ( _T, [Year] = EARLIER ( [Year] ) && [Month] = EARLIER ( [Month] ) ),
                [Date]
            )
    )
RETURN
    _T1

      Measure:

      Rolling Total = SUMX(FILTER(ALL('Sample'),'Sample'[Date]<=MAX('Date'[Last Date each Month])),'Sample'[QTY])

      Result is as below.

       

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

        Thanks For the response.

        This solution meets 90%  and only issue was i dont want to sum up Quantity.

        ie expected result shown below 

         

        Month  Count

        Jan        1000

        Feb       1000

        Mar       1000

        Apr        500

        May       500

        Jun        500

         

        The above one is based on the file you shared