Forum Discussion

Varan_15's avatar
Varan_15
Icon for Helper III rankHelper III
2 years ago
Solved

last available data based on month selection

Hi All,

 

I have calendar table and sales table so created relationship based on dates.

calendar date is used in slicer as month format , Now there is requirement if i select any month it should show that month last day of value  or if i select more month then sum of all those months last day value should show in card result.

 

EX:

sales table:

DateProductSalesetc columns..
01/01/202410160 
02/01/202410180 
31/01/202410190 
02/02/202410140 
28/02/202410126 
04/03/202410115 
31/03/202410178 
01/04/202410136 
02/04/20241015 
03/04/20241014 

 

expected result like:

if i select slicer month (jan, mar, april) then sum of last date value should show. (172)

if i select only Jan then jan month last value should show (90)

 

thanks in advance.

Vara

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    Varan_15 

    you can try to create a column in fact table

     

    Column = if('Table'[Date]=maxx(FILTER('Table',year('Table'[Date])=year(EARLIER('Table'[Date]))&&month('Table'[Date])=month(EARLIER('Table'[Date]))),'Table'[Date]),"y")
     
    then create the measure
    Measure = sumx(FILTER('Table','Table'[Column]="y"),'Table'[Sales])
     
     
    pls see the attachment below
     
     

     

3 Replies

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

    Varan_15 

    you can try to create a column in fact table

     

    Column = if('Table'[Date]=maxx(FILTER('Table',year('Table'[Date])=year(EARLIER('Table'[Date]))&&month('Table'[Date])=month(EARLIER('Table'[Date]))),'Table'[Date]),"y")
     
    then create the measure
    Measure = sumx(FILTER('Table','Table'[Column]="y"),'Table'[Sales])
     
     
    pls see the attachment below