DAX To calculate to date for some categories and current Mth for others

[tvaishnav]

I have data as follows :

Name of table is 'Net Cash Position'

Metric | Mth | Value

Billed  | Jan 2022        |100

Billed  | Feb 2022        |200

Billed  | Mar 2022       |300 

AR      | Jan 2022        |50

AR      | Feb 2022        |25

AR      | Mar 2022        |30

There is date table that is filtering Mth column.

I want to display this data in matrix as follows :

Billed | 600 (To date calculation)

AR     | 30   (Value per current month that is selected in data slicer)

I can do this in SQL but I am looking for DAX solution.

I tried this solution :

Value  = SUMX('Net Cash Position',
                            IF('Net Cash Position'[Metric] =  "AR"
                            ,('Net Cash Position'[Val])
                            ,CALCULATE(SUM('Net Cash Position'[Val]),'Net Cash Position'[Mth]<= MAX('Date'[Date]))))

But shows results as if condition is always true.

[lbendlin]

Value = 
var m = max('Net Cash Position'[Mth])
return if(SELECTEDVALUE('Net Cash Position'[Metric])="AR",sum('Net Cash Position'[Val]),sumx(filter(allselected('Net Cash Position'),'Net Cash Position'[Mth]<=m && 'Net Cash Position'[Metric]="Billed"),'Net Cash Position'[Val]))

see attached

[tvaishnav]

@lbendlin That certainly helps. I read more about the functions you used and it does make sense. However, it seems that DAX code does not give desired result if I have relationships coming in. Can you look at the modified pibx file that I have attached? I tried attaching .pbix file with sample data but I get a error message. I really appreciate your time.

I have a model that looks like this:

I have slicer on job name and month. The formula stops working in this set up. User would select one value in both job and name slicer.

[lbendlin]

Post your sample pbix on a file service.

[tvaishnav]

Not sure why this did not occur to me. 🙂

https://drive.google.com/file/d/1ZMCd0li0ItLHxkWYEImGqAnqJQQ4CMRU/view?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

Download the PBI file from here.

Hope this helps.

[tvaishnav]

Thank you so much for this. I tested this and it worked great. I did modify this a bit to change measure from year to date  to To date from the beginning of project.

Thank you so much for your time.