Forum Discussion

nhuda's avatar
nhuda
Icon for Helper II rankHelper II
2 years ago
Solved

Cumulative calculation not returning data when no data for period selected

Hello, I have some a table report where data is being displayed for the period selected (using date slider), and also cumulative data for each row groups. Cumulative calculation works fine except wh...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi nhuda 

     

    Please try this:

    First of all, add a new table:

     

    Table = CALENDAR(MIN('vw_PRBI_Report_WeldRepairStats'[TestedDate]),MAX('vw_PRBI_Report_WeldRepairStats'[TestedDate]))

     

    Then I've made some changes to your original measure:

     

    Cumulative Tested = 
VAR _Select = SELECTEDVALUE(vw_PRBI_Report_WeldRepairStats[Project])
VAR _pStartDate = MINX(FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),'vw_PRBI_Report_WeldRepairStats'[Project]=_Select),[ProjectStartDate])
VAR _toDate=MAXX(ALLSELECTED('Table'),'Table'[Date])

VAR __table = 
SUMMARIZE('vw_PRBI_Report_WeldRepairStats',[MaterialGroup],"__value",
    SUMX(
        FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]>=_pStartDate && 
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]<=_toDate &&
        'vw_PRBI_Report_WeldRepairStats'[Project]=_select && 
        'vw_PRBI_Report_WeldRepairStats'[MaterialGroup] = SELECTEDVALUE('vw_PRBI_Report_WeldRepairStats'[MaterialGroup]))
        ,IF(ISBLANK([Tested]) || [Tested] = 0, 0, [Tested])
    )
)
RETURN
IF(
    HASONEVALUE(vw_PRBI_Report_WeldRepairStats[MaterialGroup]),
    SUMX(
        FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]>=_pStartDate && 
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]<=_toDate &&
        'vw_PRBI_Report_WeldRepairStats'[Project]=_select && 
        'vw_PRBI_Report_WeldRepairStats'[MaterialGroup] = SELECTEDVALUE('vw_PRBI_Report_WeldRepairStats'[MaterialGroup]))
        ,IF(ISBLANK([Tested]) || [Tested] = 0, 0, [Tested])
    ),
    SUMX(__table,[__value])
)

     

    Then use the 'table'[date] as a slicer:

    The result is as follow:

    Best Regards

    Zhengdong Xu
    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
2 years ago

Hi nhuda 

 

Please try this:

First of all, add a new table:

 

Table = CALENDAR(MIN('vw_PRBI_Report_WeldRepairStats'[TestedDate]),MAX('vw_PRBI_Report_WeldRepairStats'[TestedDate]))

 

Then I've made some changes to your original measure:

 

Cumulative Tested = 
VAR _Select = SELECTEDVALUE(vw_PRBI_Report_WeldRepairStats[Project])
VAR _pStartDate = MINX(FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),'vw_PRBI_Report_WeldRepairStats'[Project]=_Select),[ProjectStartDate])
VAR _toDate=MAXX(ALLSELECTED('Table'),'Table'[Date])

VAR __table = 
SUMMARIZE('vw_PRBI_Report_WeldRepairStats',[MaterialGroup],"__value",
    SUMX(
        FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]>=_pStartDate && 
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]<=_toDate &&
        'vw_PRBI_Report_WeldRepairStats'[Project]=_select && 
        'vw_PRBI_Report_WeldRepairStats'[MaterialGroup] = SELECTEDVALUE('vw_PRBI_Report_WeldRepairStats'[MaterialGroup]))
        ,IF(ISBLANK([Tested]) || [Tested] = 0, 0, [Tested])
    )
)
RETURN
IF(
    HASONEVALUE(vw_PRBI_Report_WeldRepairStats[MaterialGroup]),
    SUMX(
        FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]>=_pStartDate && 
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]<=_toDate &&
        'vw_PRBI_Report_WeldRepairStats'[Project]=_select && 
        'vw_PRBI_Report_WeldRepairStats'[MaterialGroup] = SELECTEDVALUE('vw_PRBI_Report_WeldRepairStats'[MaterialGroup]))
        ,IF(ISBLANK([Tested]) || [Tested] = 0, 0, [Tested])
    ),
    SUMX(__table,[__value])
)

 

Then use the 'table'[date] as a slicer:

The result is as follow:

Best Regards

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

nhuda's avatar
nhuda
Icon for Helper II rankHelper II
2 years ago

Hi Anonymous ,

 

Sorry for the delay in answering you, I was away for 7 days.

I just tried your formulae and it's making the Cumulative number contant irrespective of changes to both the "From" and "To" dates selected in the Date Selector. It should go higher when "To" date is changed (and there's additional data). When only "From" date is changed in the selector, it should stay the same because from the begiining of the Project to the "To" date we have the same number of Tested.

Not sure why you got the good result and mine is not good.

But I appreciate your time and effort to resolve my issue.

 

Regards,

nhuda