Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with measures by Month

Hello all!

 

I have a situation, I hope someone can help me with this doubt

 

I have a visual table in my report that show some columns like this:

I created a measure by Month using this sintaxis,

July = CALCULATE([Amount],'Date'[Month Name]="July") one by Month and another measure for the YTD 
Amount YTD = TOTALYTD([Amount],'Date'[Date])
In the filters, I have the month and the year, and when I select one of the month, for example Feb; the visual show me all the months; is possible to show until the month selected, for example If i select Feb, show Jan and Feb, the others  months dont show data.
Thanks for the help
Monica
 

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    In order to prevent the relationship between your date table and the fact table from having other uses, I created another date table with CALENDARAUTO() and used the month as a filter.

    Create this measure and put it in the matrix:

    _Amount =
IF (
    NOT ( ISFILTERED ( 'Date'[Year] ) ),
    BLANK (),
    IF (
        ISFILTERED ( _Date[Date].[Month] ),
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER (
                'Table',
                MONTH ( 'Table'[Date] ) <= SELECTEDVALUE ( '_Date'[Date].[MonthNo] )
            )
        ),
        SUM ( 'Table'[Amount] )
    )
)

    Attached the modified sample that you can refer: sample file.pbix 

     

    Best Regards,
    Yingjie Li

    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
    6 years ago

    Thanks a lot,

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous 

     

    On your filter select as shown below and select Before.

     

     

    Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

     

    Regards,
    Pranit

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi Anonymous ,

    You can create this control measure, put it in the visual filter and set its value as 1:

    control = 
IF(
    SELECTEDVALUE('Date'[Date].[MonthNo]) >= SELECTEDVALUE('Table'[Date].[MonthNo]),1,0
)

    Attached a sample file that you can refer: sample.pbix

     

    Best Regards,
    Yingjie Li

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