Forum Discussion

PKostta's avatar
PKostta
Regular Visitor
2 years ago

Monthly values not showing

Hi, 

 

Im trying to calculate the monthly sales from the previous year but it only shows the total sum not the monthly values.

For testing i´ve created 2 measures and the one without the FILTER works fine with monthly values, the measure with a FILTER shows only the total:

 

 v_Sum_CDML_PAID_AMT_AMB_YTD_LY v_Sum_CDML_PAID_AMT_YTD_LY
2024-Jan 500
2024-Feb 750
Total6001250

 

Measure that is working:

v_Sum_CDML_PAID_AMT_YTD_LY =
VAR first_date =
    FIRSTDATE ( DATEADD ( Calendario[Date], -12, MONTH ) )
VAR last_date =
    LASTDATE ( DATEADD ( RAID_ClaimsDrivers_CLCL[CLCL_INPUT_DT], -365, DAY ) )
RETURN
    IF (
        ISBLANK ( first_date ) || ISBLANK ( last_date ),
        BLANK (),
        CALCULATE (
            SUM ( RAID_ClaimsDrivers_LINE[CDML_PAID_AMT] )
            , DATESBETWEEN ( Calendario[Date], first_date, last_date )
        )
    )
 
Measure with FILTER that doesnt show the monthly values, only the total:
v_Sum_CDML_PAID_AMT_AMB_YTD_LY =

VAR first_date =
    FIRSTDATE ( DATEADD ( Calendario[Date], -12, MONTH ) )
VAR last_date =
    LASTDATE ( DATEADD ( RAID_ClaimsDrivers_CLCL[CLCL_INPUT_DT], -365, DAY ) )
   
 RETURN
    IF (
        ISBLANK ( first_date ) || ISBLANK ( last_date ),
        BLANK (),
        CALCULATE (
            SUM ( RAID_ClaimsDrivers_LINE[CDML_PAID_AMT] )
            , FILTER(RAID_ClaimsDrivers_CLCL, RAID_ClaimsDrivers_CLCL[COBERTURA]="Ambulatório")
            , DATESBETWEEN ( Calendario[Date], first_date, last_date )
        )
    )
 
Can anyone help me to understand why measure v_Sum_CDML_PAID_AMT_AMB_YTD_LY is not working properly?
 
Kind Regards,
Pedro
 
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PKostta ,

     

    According to your code, I think there are three tables are used in your code, Calendario/RAID_ClaimsDrivers_CLCL/RAID_ClaimsDrivers_LINE.

     

    When you add a filter from RAID_ClaimsDrivers_LINE table, your. So I think your issue should be caused by your data model.

     

    It is hard for us to find the solution only based on your code.

     

    Here I will give you some suggestion on your code. I suggest you to try ALL() function and then add some filter to link RAID_ClaimsDrivers_LINE table with the date parameter and try again.

     

    If this reply still couldn't help you solve your issue, please share a sample file with us or show us a screenshot with the result you want. This will make it easier for us to find the solution.

     

    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.