Forum Discussion

Vidya1882's avatar
Vidya1882
Frequent Visitor
2 years ago
Solved

Total value for last 6 months.

Hi ,

Delivered total value = if(sum(DMT_HCP_PERSONA_NEW[DELIVERED]) >= 1 , 1,0)

6 month delivered total value =
VAR Last6Months = DATESINPERIOD('Calendar'[Date], LASTDATE('Calendar'[Date]), -6, MONTH)
RETURN
SUMX( VALUES(DMT_HCP_PERSONA_NEW[HCP_PERSONA_HCP_MDM_ID]), CALCULATE( [Delivered total value], Last6Months ) )

if i used above measure it give the below result 

HCP_IDJulAugSepOctNovDecTotal
51111111
101001001
120011011
241111111

 

i need the total value for last 6 months based on id.below is my sample requirement result.

HCP_IDJulAugSepOctNovDecTotal
51111116
101001002
120011013
241111116

 

i need a total value for each id.but its give the total value is 1 for each id.please give the correct one.

thanks,

Vidya

Need Help
  • sergej_og's avatar
    sergej_og
    2 years ago

    like this?


     - First I have taged my facts with 1 or 0 (calculated column)
     - then I used this formula:

    CALCULATE(
    SUM(yourFactTable[Tag-column]),
    FILTER(
        yourFactTable, 1),
    DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -6, MONTH))


    Hope you got the idea.

    Regards

4 Replies

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    2 years ago

    Hey Vidya1882 ,
    you expect stg like this, right?

    ...last 6 mths 


    ...last 4 mths


    used this formula for last 6 month:

    Sum_last_6m = CALCULATE(
                    [Sum_Amt],
                    DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -6, MONTH)
)

    Pls adjust for your needs. 

    Regards

    • Vidya1882's avatar
      Vidya1882
      Frequent Visitor
      2 years ago

      Hi sergej_og 
      Delivered total value = if(sum(DMT_HCP_PERSONA_NEW[DELIVERED]) >= 1 , 1,0)
      if i use above this measure its give total 1 or 0.it not give the row of total value .for example: DMT_HCP_PERSONA_NEW[DELIVERED] is 65 for id 5 for july month, so this is greater than 1 right ,so it return 1 otherwise 0.same method for all months.in this case for all months value is 1 for 5 id .it should be total is 6.but it give the 1. need exact value is above

      HCP_IDJulAugSepOctNovDecTotal
      51111116
      101001002
      120011013
      241111116
      • sergej_og's avatar
        sergej_og
        Icon for Super User rankSuper User
        2 years ago

        like this?


         - First I have taged my facts with 1 or 0 (calculated column)
         - then I used this formula:

        CALCULATE(
    SUM(yourFactTable[Tag-column]),
    FILTER(
        yourFactTable, 1),
    DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -6, MONTH))


        Hope you got the idea.

        Regards