get the last day sale of every month

[Anonymous]

Hi, I want to get the last day sale of each month from a date table as DimDate and a Fact Table with all aggregates. anyone kindly hlep me how to achieve this. i have tried Calculate(sum( FactTable[TotalSales]), LastDate(Dimdate[DateColumn]). 

it is giving me the total sales of each month but not the sales of last date of each month. 

Your ideas on this will be highly regarede.

[v-chenwuz-msft · Accepted solution]

Hi  HumzaIftikhar,

Try this measure

lastday total =

    CALCULATE(SUM('Fact Table'[Sales]),FILTER('Fact Table','Fact Table'[Date]=ENDOFMONTH('Fact Table'[Date])))

You can try it with the demo I provided, please let me know if you have any questions.

Best Regards!

Community Support Team _ chenwu zhu

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

[Ashish_Mathur]

Hi,

Do these measures work?

Total = SUM(Data[Balance])

Balance on last date = CALCULATE([Total],LASTNONBLANK('DimDate'[DateColumn],CALCULATE([Total]))))

To the visual, ensure that you drag the Month name from the DimDate table.

[Anonymous]

Hi @Ashish_Mathur , Thanks for being here. i tried this measure but it is giving me the same results. Here is a screen shot of that output. i want the sale of lastday of each month but it is giving me the total sale of each month. 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.