Forum Discussion

syaifulefendi's avatar
syaifulefendi
Frequent Visitor
3 years ago
Solved

Getting filter result from selected period

Hi Guys 

I need your magic help here .

actually im having a data ( attached below )

region_nmbranchprovinsikabkotaitem_nmbrand_nmmonthmetricvalval_target
REGION 3MAKASSARSULAWESI BARATPOLEWALI MANDARITEM 6BRAND 201/08/2021Quantity26851937
REGION 1PEKANBARUKEPULAUAN RIAUKARIMUNITEM 6BRAND 201/04/2022Quantity5290242342
REGION 3MAKASSARSULAWESI BARATPOLEWALI MANDARITEM 4BRAND 201/11/2022Quantity38694059
REGION 3MANADOSULAWESI BARATMAMUJU UTARAITEM 6BRAND 201/09/2021Quantity614563
REGION 3MAKASSARSULAWESI BARATPOLEWALI MANDARITEM 5BRAND 101/07/2022Quantity24082177
REGION 3MANADOMALUKU UTARAKOTA TERNATEITEM 3BRAND 101/10/2022Quantity8281018
REGION 3MANADOMALUKU UTARAKOTA TIDORE KEPULAUANITEM 2BRAND 201/12/2021Quantity179168
REGION 3MAKASSARSULAWESI BARATMAJENEITEM 5BRAND 101/09/2022Quantity461519
REGION 3MAKASSARSULAWESI BARATMAMASAITEM 6BRAND 201/10/2022Quantity13041618

 

here my goals is , when i select a period on filter  , let say january 2022. 
the chart and data only showing data within 1 year ( january 2021 - january 2022 )

kindly help me for that , help with attached powerbi file would be a great help

many thanks 
SE

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi syaifulefendi 

    You can create a date table first

    e.g 

    Table 2 = CALENDAR(DATE(YEAR(MIN('Table'[month])),1,1),DATE(YEAR(MAX('Table'[month])),12,31))

    The relationship between two table

     

    Then create a measure

    Measure = var a=MIN('Table 2'[Date])
var b= EOMONTH(a,-13)+1
return CALCULATE(SUM('Table'[val]),CROSSFILTER('Table'[month],'Table 2'[Date],None),'Table'[month]<=EOMONTH(a,0)&&'Table'[month]>b)

    Then put the measure to the table visual and put the column of date table to slicer.

    Output

     

    Best Regards!

    Yolo Zhu

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi syaifulefendi 

    You can create a date table first

    e.g 

    Table 2 = CALENDAR(DATE(YEAR(MIN('Table'[month])),1,1),DATE(YEAR(MAX('Table'[month])),12,31))

    The relationship between two table

     

    Then create a measure

    Measure = var a=MIN('Table 2'[Date])
var b= EOMONTH(a,-13)+1
return CALCULATE(SUM('Table'[val]),CROSSFILTER('Table'[month],'Table 2'[Date],None),'Table'[month]<=EOMONTH(a,0)&&'Table'[month]>b)

    Then put the measure to the table visual and put the column of date table to slicer.

    Output

     

    Best Regards!

    Yolo Zhu

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